"TLC"

One of the very first tasks I like to do when presented with a new SQL Server Instance is to review what’s been happening of late in the SQL Server Error Log file. Doing so can often provide a quick insight into items that require my attention. It’s not uncommon to encounter backup failure messages, performance warnings or even nasty error messages.

If the SQL Server Instance has not been given a dose of TLC (no not that 90′s R&B group) for some time, you can often encounter a rather large error log file. This can be a pain to work with because it can take an age to open up in management studio and navigation can be cumbersome.

Managing the SQL Server Error Log

So in order to make working with your SQL Server Error log files easier, it’s a good idea to proactively manage their size.

1. There are two ways that you can go about this. Modify the configuration properties for your SQL Server Error Log files. (In SQL Server Management Studio simply right click the SQL Server Log folder in Object Explorer and choose configure).
2. Make use of the system stored procedure sp_cycle_errorlog to immediately cycle the error log.

By default SQL Server retains the last six sql server error log files. The number of files for retention can be modified be editing the configuration properties for the SQL Server Error log.

I’m not going to waste time re-inventing the wheel here as there are already some great tutorials available on the net via your trusty friend Google. I particularly like the general overview of SQL Server Error Logs available at SQL Server Pedia.

Cut that large log down to size

The way I like to do things is even before looking to define a suitable log management scheme for a server, as soon as I encounter a large error log file I like to cycle that log right away using sp_cycle_errorlog. This gets things under control immediately. Simply execute:

EXEC master.sys.sp_cycle_errorlog;

Give your SQL Server Logs some TLC today…