HOW TO: Cycle the SQL Server Error Log's Automatically

Large error logs can degrade the UI performance when inspecting via the SQL Server tools and when auditing via various third party tools.  Keeping the SQL Server error log to a reasonable size is considered by RockSolid as good practice.

To have RockSolid automatically cycle the SQL Server error log, keeping it a reasonable size requires two configuration steps.  First, you must configure RockSolid to raise events if the SQL Server error log exceeds a configured size.  Secondly, to automatically cycle, you must configure appropriate automation change control settings.

STEP #1 - Detect large SQL Server Error Log Files

To detect a large current SQL Server error log file you must configure RockSolid policy to "Manage SQL Server Error Log" and the "Maximum Error Log Size".  To do this, within an instance policy level (normally the group level) navigate to the policy tab and:

 

  • Click the Instance Management sub-tab
  • Scroll to the SQL Server Error Log Settings heading
  • Ensure "Manage SQL Server Error Log" is enabled (either directly of through inheritance)
  • Specify the Maximum Error Log Size that you wish to be allowed on your instances.  Once exceed, RockSolid will raise an event allow you to automate resolution.

 

NOTE: At this time you can also choose to increase the configured number of SQL Server Error Log files.  By increasing the number of error logs, when cycling more frequently you are able to maintain a longer history on the SQL Server instance.

RockSolid now monitor the current SQL Server error log file size and raise events once exceeded.

STEP #2 - Allowing RockSolid to Automatically Cycle the SQL Server Error Log File

RockSolid cannot make any automated changes to a SQL Server instance unless the "Automated Management" policy option is enabled.  Before undertaking these steps ensure this is configured.

To enable the Cycle Error Log change control, perform the following steps:

  • Navigate to Service Requests -> Manage Change Control
  • Click on the "Auto Approve" tab
  • On the Instance Group/Instance drop downs choose the relevant instance scope
  • In the "Task" drop down choose the "Cycle Error Log" task
  • Click "Add Auto Approval"

Once configure RockSolid will automatically resolve events requiring the SQL Server error log to be cycled.

STEP #3 - Closing Existing Requests

When you first enable the above change control, existing requests assigned for manual resolution will not be affected.  To have RockSolid attend to current cycle error log requests, you simply need to cancel the existing requests.  Once these are re-raised they will be automatically resolved.  To do this:

  • Go to the RockSolid home page and choose the "Task View" tab
  • Drill into the Cycle Error Log" grouping if present
  • In the service request detail view click the "Batch Update" tab
  • Ensure the task drop down shows "Cycle Error Log" tasks only
  • Hit Search
  • Select all open requests
  • Click Batch Update and change the "Status" to "Cancelled" and enter the time take in the relevant text box
  • Hit the red "Batch Update" to close these requests.  

These request will be re-raised, after which they will be automatically resolved.  Going forward, all new Cycle Error Log requests for which the relevant change control is active will be automatically resolved.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.