Setting up errorlog maintenance

December 24, 2015 at 14:00

After installing a new SQL Server instance the default configuration of the errorlog is to keep the 6 most recent files which are only re-initialized after a service restart. This means that after 6 service restarts you start to lose information, on the other hand if you very rarely restart your server/instance the errorlog can become very large, making troubleshooting harder.

Luckily there are a few things you can do to have more files available and to keep the errorlog smaller. Changing the number of files you want to keep can be done through the SSMS GUI or T-SQL command.

If you want to change this setting through the GUI

Right click on SQL Server Logs under Management and select Configure

Configure SQL Server Error Logs

Check the box next to Limit the number of error log files before the are recycled. Now you can change the Maximum number of error log files:  and click Ok.

Configure SQL Server Error Logs

If you want to do it with a TSQL statement you need to use the procedure xp_instance_regwrite to write the change in the registry. In the code example below you can see how to change this setting to 25:

To prevent the errorlog file from growing too large you need to regularly cycle the errorlog. Prior to SQL Server 2012 you could only do this by executing the following command:

You can create a job to do this. But I found a little bit more intelligent script that checks the age of the file. If the file is older than 14 days it will execute the command. you can find the script here

Starting with SQL Server 2012 you can specify a maximum filesize. The only way to change the size is through TSQL as there is no possibility in SSMS:

the above script will set the maximum filesize to 25MB.

 

Tags: