Cycling SQL Server Error Log

I have a daily check list that I go through everyday. And checking the SQL Server error log is one item on the list. Depends on how your server is configured SQL Server logs informational and error messages to the log file. By default your server is configured to have 6 log files that it keeps and when the SQL Server Service starts it removes the oldest log file. In the meantime if your server is up for a long time the information in that log file just keeps growing and makes the log file hard to open from remote workstation.

In the environments that I support I change the configuration so that I keep more than 6 files. I set up to store 99 files (which is the max allowed files for the SQL Server error log). Then I create a job that runs every night right after midnight. This way when I come in every morning there is a brand new log file and since the daily log files are much smaller it makes the file easy to open and check. I use the script below to change the number of log files (note you can do this using SSMS GUI and just make a right click on SQL Server Logs under Management folder than click on Configure and change the value to what you need). After the change using second script I create SQL Server Agent Job that is scheduled to run right after midnight to cycle the error log. This make the daily task of checking the error logs easier (at least for me). Use the scripts below in a test environment and once you find out it could add some value to your daily tasks you could use it in your production environment.

HTH,
Bulent

Script 1:
This changes the number of log files stored to a value you set at the end and uses extended stored procedure to update the registry. Remember this can be done through GUI but it does executed the same in the background.

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 60

GO



Script 2:
This script creates a job (make sure you sql server agent is running) and schedules it to run at 12:00:01 am everyday.

USE [msdb]

GO

/****** Object: Job [A DBA SQL Server Error Log Cycle] Script Date: 01/27/2012 14:01:38 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [Database Maintenance] Script Date: 01/27/2012 14:01:38 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'

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

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'A DBA SQL Server Error Log Cycle',

@enabled=1,

@notify_level_eventlog=2,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Cycle sql serve error log and create a new error log file',

@category_name=N'Database Maintenance',

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

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

/****** Object: Step [Cycle Error Log] Script Date: 01/27/2012 14:01:38 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cycle 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 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'Cycle Error Log',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20111013,

@active_end_date=99991231,

@active_start_time=1,

@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