Cycle Those Logs!

7 08 2015

The SQL Server Error log is an extremely useful troubleshooting tool and often the first stop when trying to identify any issues. Beyond troubleshooting a DBA should be reviewing that log file on a weekly basis at a minimum, preferably daily on critical systems.

The problem is that by default the SQL Server Error log only rolls its log over when the SQL Server service restarts. Given that the goal on any production database is to keep it up and running, we are left with a log file that has months worth of data in it. So when things to go down the log file is to large to even open let alone find useful information in.

Microsoft provides a stored procedure for us to deal with this problem, sp_cycle_errorlogs. Executing this command causes the SQL Server error log to roll over and start a new log file.

Create a job that runs daily at midnight that executes the stored procedure sp_cycle_errorlogs. A log file will be created for each day, with that it is important to configure the number of logs to be retained.

Expand the Management tree in SSMS and right-click on SQL Server Logs node and select Configure.
Select the check box to limit the number of error log files and set the value to the number of days you want to keep logs local for. Typically I will configure this value to 30.