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.

Config-30-days-logs

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).

Config-30-days-logs2

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_midnigt_job

 

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

Happy debugging!

 

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s