Cycle SQL Server Error Logs

I saw this again recently and see it too often in environments so wanted to take a second to remind everyone to cycle their error logs on a regular basis. SQL Server keeps error logs and when you reboot or restart SQL Server services the logs are cycled and a new one is created. Depending on how many logs you have configured for SQL Server to have this may include removal of the oldest log as well. Since many of pride ourselves on keeping our SQL Servers up and running, reboots may be few and far between thus our logs get large in size.

When they grow out of control it can require long wait times for the logs open to even view them. An easy way to keep this from happening is to cycle them routinely. You can easily automate these by creating a SQL Agent job to cycle the log to a new one on a regular basis whether it is monthly, weekly or even daily.

First double check and determine how many error logs you want to maintain.

For this example, Ill be keeping one for each month and setting up a monthly job to cycle the logs.  This means I will want to configure the maximum number of error logs to 12. Now something to keep in mind is that when the server is rebooted you will still only have 12 logs, you may end up loosing a log you may want to keep. I am using 12 as an example but would suggest you have a buffer and go with a number like 16 or 20 to be safe.

Using T-SQL

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 12 
GO

Here is the script to create the job. It simply creates and schedules an Agent job to run as system procedure called EXEC master.sys.sp_cycle_errorlog .

USE [msdb]
GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MaintenanceCycleErrorlogs',

 @enabled=1,

 @notify_level_eventlog=0,

 @notify_level_email=0,

 @notify_level_netsend=0,

 @notify_level_page=0,

 @delete_level=0,

 @description=N'No description available.',

 @category_name=N'[Uncategorized (Local)]',

 @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Recycle the SQL Server Error Log',

 @step_id=1,

 @cmdexec_success_code=0,

 @on_success_action=1,

 @on_success_step_id=0,

 @on_fail_action=2,

 @on_fail_step_id=0,

 @retry_attempts=0,

 @retry_interval=0,

 @os_run_priority=0, @subsystem=N'TSQL',

 @command=N'EXEC master.sys.sp_cycle_errorlog;',

 @database_name=N'master',

 @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monthly',

 @enabled=1,

 @freq_type=16,

 @freq_interval=1,

 @freq_subday_type=1,

 @freq_subday_interval=0,

 @freq_relative_interval=0,

 @freq_recurrence_factor=1,

 @active_start_date=20111110,

 @active_end_date=99991231,

 @active_start_time=0,

 @active_end_time=235959


IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

If you are not doing this in your environment, please take the time to set it up. It’s a simple maintenance task you should not neglect.