How to prevent enormous SQL Server error log files

Question: Some of the SQL Server instances I manage routinely have extremely large (multiple gigabytes) error logs because they are rebooted so infrequently. Trying to open an error log that large is really problematic. Is there a way that the error logs can be made smaller?

Answer: I completely sympathize with you. Very often when dealing with client systems we encounter similar problems. Thankfully there is an easy solution. (See also, "Choosing Default Sizes for Your Data and Log Files" and "Why is a Rolled-Back Transaction Causing My Differential Backup to be Large?").

The number of error logs is set to 6 by default, and a new one is created each time the server restarts. Old ones are renamed when a new one is created and the oldest is deleted. As you’ve noticed, this can lead to extremely large error log files that are very cumbersome to work with.

There is a registry setting ‘NumErrorLogs’ that controls the number of error log files to keep in the LOG directory. This can easily be changed through Management Studio. In Object Explorer for the instance, navigate to Management then SQL Server Logs. Right-click and select Configure as shown below.

This brings up the Configure SQL Server Error Logs dialog. Check the ‘Limit the number of error log files before they are recycled’ box and set your desired number of files – I usually choose 99. See the screenshot below.

This doesn’t solve the size problem, but does mean that more error logs will be kept around. To solve the size problem, create a SQL Server Agent job that executes at some point every day and runs the command

    EXEC sp_cycle_errorlog;    GO

This causes a new error log file to be created and will prevent the error log becoming overly large on systems that do not reboot for a long time.