Cycle SQL Server Error Logs

The SQL Server error log is notorious for growing really large when certain things happen; like repeated login failures, stack dumps or other errors. In cases when trace flag 3226 is not turned on and there are hundreds of databases being backed up the error log can also grow really big.  When the log gets big, it makes it difficult to find real issues, much slower and it can fill up your disk drive.

By default a SQL Server instance will keep up to 6 error logs on hand—so if you’re dealing with highly sensitive information or an environment where auditing is very important you might want to push this number up.

Every time SQL Server is started, the current error log is renamed to errorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes errorlog.3, and so on. sp_cycle_errorlog enables you to cycle the error log files without stopping and starting the server.  Execute permissions for sp_cycle_errorlog are restricted to members of the sysadmin fixed server role.

Once you’ve determined an interval at which to regularly cycle your logs, cycling them is actually quite easy. To tackle this need I just set up a SQL Server Agent Job that I run (typically) weekly, and which uses the following to cycle the error log:

-- Error Log:
USE master;
GO
EXEC master.sys.sp_cycle_errorlog;
GO

You can also use a similar technique to cycle the SQL Server Agent Log as well:

-- SQL Server Agent Error Log:
USE msdb;
GO
EXEC dbo.sp_cycle_agent_errorlog;
GO