SQL Server Error Logs

What is the Error Log?

SQL Server maintains its own error logs that contain messages describing informational and error events.

These are simple text files stored on disk, but it’s best to view them using the facilities provided by SQL Server to prevent any SQL operations from being blocked by opening one of these files in a text editor. Also, the error log files can become quite large – SQL Server will stream the files into the Log File Viewer whereas notepad will open the file into a single buffer and consume a great deal more memory.

A new error log file will be created when one of two things happens:

1. The SQL Server service is started
2. sp_cycle_errorlog is called

Once this happens, any pending writes to the current log file will complete and a new log file will be created. The actual error log files can be found at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n.

Viewing the Error Log

The Basics

Locating the error log

Viewing the Error Logs

Viewing the error log is a simple operation:

1. Connect to the SQL Server using SQL Server Management Studio
2. Expand the Server > Management > SQL Server Logs
3. Double click on a log file

This will open up the log file. One thing to keep in mind is that by default this log file will only cycle when the SQL Server service has started. If this is a production server and you haven’t made any configuration changes, then this file could be quite large and take a long time to open.

Refining Your View

The Error Log Filter Box

Filtered Error Log Results

By default, you will end up with a view of the entire contents of one or more log files. This probably isn’t very useful to you unless you want to scan the contents for something very recent. On a busy server, though, there could be hundreds of thousands of lines in a single error log file.

Clicking the filter button opens up the Filter Settings window. This gives you the opportunity to filter by User, Computer, Start and End Date, the message text, and the message source. Given that there are so many different sources for the messages in the error log, the ability to filter the error log is a huge boon. Once you’ve entered your filter criteria, check ‘Apply filter’ and then click OK. Just make sure you check ‘Apply filter’ otherwise SQL Server Management Studio will happily discard all of your filter criteria and not filter any of the data.

Care & Feeding of the Log File

Automated Log File Rotation

To prevent the log files from growing too large it’s necessary to rotate them on a regular basis. Restarting the SQL Server service every week at 3:34 AM on a Sunday isn’t advisable, so there must be some other option. Thankfully, there is. The log file can be automatically rotated using the sp_cycle_errorlog system stored procedure.

Depending on who you talk to, different people will tell you to cycle the error log on a daily, weekly, or monthly schedule. The frequency really depends on your particular system and how much data accumulates in the logs during the course of regular operations. The more writes to the error log, the more often you should cycle the log. To cycle the logs, create a new Agent Job with a single T-SQL task (or combine it with regular daily or weekly maintenance).

Number of Log Files to Maintain

Depending on the amount of storage space you have available and the amount of activity on your SQL Server, you will want to change how often you rotate your log files and also the number of log files that you want to retain. Why would you want to do this? To keep as much historical information available as you possibly can.

Configuring the Error Log

Changing the Number of Error Log Files

More importantly, how do you do this?

1. Right click on the SQL Server Logs folder in the Object Explorer
2. Select ‘Configure’
3. Check the box to ‘Limit the number of error log files before they are recycled’. This seems silly, but by default SQL Server will only keep 6 error log files before trashing them.
4. Put in a new value and click ‘OK’.

I typically keep 99 error logs before they are recycled. Why? Because I can.

Wrap Up

I hope that this has helped you understand more about the SQL Server Error Log and how you can can automate the successful maintenance of your servers’ log file information.