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
