Overview Of Cycle Error Logs

I saw this again recently, and have seen it too often in environments so I wanted to take a second to remind everyone to cycle their error logs on a regular basis. SQL Server keeps the error logs and when you reboot or restart the SQL Server services, the logs are cycled and a new one is created. Depending on how many logs you have configured for SQL Server, having this may include removal of the oldest log as well. Since many of us 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 the logs grow out of control, it can require long wait times for the logs to open and to even view them. An easy way to keep this from happening is to cycle them routinely. You can easily automate these by creating an SQL Agent job on a regular basis, whether monthly, weekly, or daily.

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

For this example, I'll 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 losing 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

-- Script to create the job scheduling the execution of a system procedure
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 spare a little time to set it up. It’s a simple maintenance task you should not neglect.