HOW TO: Automatically Set the Number of SQL Server Error Log Files

RockSolid can automatically SQL Server error log files, cycling them to ensure they are kept to an optimal size.  However when doing this you may find that information is removed in a shorter duration that would you would like.  A solution to this is to increase the number of SQL Server error log files, which allows you to cycle often but also retain a longer history.  This article describes how to use RockSolid to automatically configure the number of error log files via RockSolid policy settings.

RockSolid can be used to both detect when an instance has a configured number of SQL Server error log files which differs to the RockSolid policy setting, and also automatically configure the correct number of error log files for relevant instances.  To do this follow the instructions below.

Step #1 - Detect Incorrect Number of SQL Server Error Log Files

To automatically detect the incorrect number of SQL Server error log files perform the following steps:

  • Within the relevant level in the instance hierarchy (typically the group level) go to the "Policy" tab
  • Click the "Instance Management" sub tab
  • Set "Manage SQL Server Error Log" to ON
  • Specify the number of error log files to retain. This should be >=6 (the SQL Server default).
  • At this time you can also configure the optimal size of the SQL Server error log file to enabling automatic cycle of this.  Otherwise enter 0 in the error log size to disable automatic cycling.
  • Click save to retain the policy

Step #2 - Enabling Automatic Configuration

To enable the automatic configuration within RockSolid several conditions must be true.  These are described here:

Once this has been enabled you can configure the necessary change control. To do this:

  • Go to the Service Requests -> Managed Change Control menu
  • Click the "Auto Approve" tab
  • Choose the relevant site/group/instance for which you wish to grant this change control
  • Choose the "Instance Configuration" option in the Task drop down
  • Choose the "Change # Error Log Files" in the Sub Task drop down
  • Click the "Add Auto Approval" button to add this change control

IMPORTANT: Any existing service requests raised prior to granting this change control may not auto deploy (as they may have already been assigned for manual resolution).  If required cancel any existing service requests for this task using Batch Update.  The requests will re-raise and be processed automatically.



Have more questions? Submit a request


Please sign in to leave a comment.