Recycle your SQL Server error logs every day

Yesterday, I came across a good tip on how to organize your SQL Server Error logs. By default, the error logs are reset when the database engine are restarted or manually recycled. When error logs are recycled, the current log is archived and a new log file is created and set as Current. By default, SQL server is keeping six error logs, but this is configured as shown in the screenshot below.


When logging errors and other activity, it is very wise to organize the on- day-to-day basis. This will help you to find errors for a given date of customer or vendor come to you some days after the error occurred. Software vendors often come to you as database administrator some days to several weeks after an error has occurred, and if you have loads of data in one separate file (perhaps several GB) it is difficult to handle the log file. However, if you have organized the log files with one log file per day, you will save lots of time and do not have to wait too much when the log file loads into the client.

The number of days kept will be quite subjective, but a month of log files should be any problem. If this slows down the server or take up too much disk space, reduce the number of days to 14 days. Personally, I have set this to 30 days (one month).


In addition to these settings, we need to create a SQL Agent job that is scheduled nightly which perform a recycle of the error logs (archive + create new current log).



Create a new schedule that runs midnight and test this job.

Happy debugging!



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s