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:
- The SQL Server service is started
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
Viewing the Error Log
Viewing the error log is a simple operation:
- Connect to the SQL Server using SQL Server Management Studio
- Expand the Server > Management > SQL Server Logs
- 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
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_errorlogsystem 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.
More importantly, how do you do this?
- Right click on the SQL Server Logs folder in the Object Explorer
- Select ‘Configure’
- 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.
- Put in a new value and click ‘OK’.
I typically keep 99 error logs before they are recycled. Why? Because I can.
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.