SQL Server Error Logs

I read a blog post this morning on SQLServerCentral by Tim Radney about SQL Server Error Logs.  Tim talks about increasing the number of error logs in SQL Server.  One thing that came to mind to take this one step further and is just as simple to do is to ‘Cycle’ the error log. 

On all of the SQL servers I manage I have a job (the definition for which is below) that cycles the error log at 00:00 daily. 

USE [msdb]

GO

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'(00:00) Maintenance - CycleErrorLogs')

EXEC msdb.dbo.sp_delete_job @job_name= N'(00:00) Maintenance - CycleErrorLogs', @delete_unused_schedule=1

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'(00:00) Maintenance - CycleErrorLogs',

            @enabled=1,

            @notify_level_eventlog=2,

            @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'CycleLogs',

            @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=1,

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

            @command=N'EXEC sp_cycle_errorlog;

GO',

            @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'Daily',

            @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=20060922,

            @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

It allows me to easily view just one day’s worth of information as and when required.  This is something I have done for years now since I was once unable to view SSMS 2005 because of the sheer size and number of records in it.

Which takes me onto another quick tip and one which I picked up from an old colleague and good friend Chris Taylor http://www.chrisjarrintaylor.co.uk with regard to job names.  The eagle eyed amongst you will have noticed this already but I include the time the job runs in the job name.  I find this incredibly useful as it gives you the ability to see in SSMS when the job runs without viewing the job properties.

In this case I can see that the cycle error log job (00:00) Maintenance – CycleErrorLogs runs at midnight.  This can be easily adapted to (Every 3 Hours) (01:00 Sat) etc. etc. It has helped me and others I work with and have worked with in the past, give it a go and let me know what you think!

Enjoy

Chris