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
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
http://d1kpgdt94igfig.cloudfront.net/wp-content/uploads/2009/10/Error-Logs-01.jpg
Locating the error log
|
http://d1kpgdt94igfig.cloudfront.net/wp-content/uploads/2009/10/Error-Logs-02.jpg
Viewing the Error Logs
|
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
http://d1kpgdt94igfig.cloudfront.net/wp-content/uploads/2009/10/Error-Log-Filter-01.jpg
The Error Log Filter Box
|
http://d1kpgdt94igfig.cloudfront.net/wp-content/uploads/2009/10/Error-Log-Filter-02.jpg
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.
http://d1kpgdt94igfig.cloudfront.net/wp-content/uploads/2009/10/Error-Logs-Change-Number-01.jpg
Configuring the Error Log
|
http://d1kpgdt94igfig.cloudfront.net/wp-content/uploads/2009/10/Error-Logs-Change-Number-02.jpg
Changing the Number of Error Log Files
|
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.
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.