Recycling more… SQL Server (Agent) Logs


 

Some of the recommendations for better administering the logs of a SQL Server include

  • adding more logs than the default value of 6
  • executing a periodic rotation at a given point in a time instead of relying of the system only

Here are then a few snippets to perform the two tasks.

First, the number of logs is set up through a registry entry. This is the official script that SQL Server management studio writes when you push the ‘script’ button on the properties window of the SQL Server Logs entry in the Management folder.

I choose a value of 30 that gives me roughly an entry per day during one month. Most recommendations will make this value vary between 7 (one week with no restart) and 99 (compliance paranoid freaks).

And now here is a SQL Server agent job that will do a SQL Server log and SQL Server agent log recycle at midnight:

The first if statement allows you to put that job in a non-standard predefined category

 

 

Do not forget that logs will be also rotated if you restart the corresponding.-service

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.