Wednesday 4 January 2012

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

10 comments:

  1. Great post Chris! I'm going to implement the error log cycling now and start using your new job naming convention. Thanks again.

    ReplyDelete
  2. Warning to those that start cycling your log - make sure you *also* implement the suggestion to retain a greater number of logs; we had a junior DBA in a previous shop implement log cycling and suddenly we found we had lost our log info from several days ago since we only had five logs with 12 hours of info each in them! {-:

    ReplyDelete
  3. Thanks for the warning Andy, good point.

    ReplyDelete
  4. Thanks for the feedback guys, glad someone found it useful.

    ReplyDelete
  5. Chris,

    Great post. I've been using this method for some time, but on some of my servers I'm getting an error message like the one below. Any idea what can be causing this?

    Thanks
    Greg

    01/08/2012 06:00:00,spid75,Unknown,Unable to cycle error log file from 'V:\MSSQL10\MSSQL\Log\ERRORLOG' to 'V:\MSSQL10\MSSQL\Log\ERRORLOG.1' due to OS error '32(failed to retrieve text for this error. Reason: 1815)'. A process outside of SQL Server may be preventing SQL Server from reading the files. As a result errorlog entries may be lost and it may not be possible to view some SQL Server errorlogs. Make sure no other processes have locked the file with write-only access."

    ReplyDelete
  6. Greg,

    Glad you like the post! What I would suggest initially is making sure that as the error says make sure the file is not in use (exclude it from antivirus etc).

    Thanks

    Chris

    ReplyDelete
  7. Chris,

    It's already been excluded from AV scanning (and other sql extensions).

    Can you suggest a way of checking, what other processes are trying to access sql server error log files?

    Thanks,
    Greg

    ReplyDelete
  8. Greg,

    I've done some tests and even opening the current error log file and making modifications but not closing it I can still cycle the error log. Has the SQL Server service account got permissions to the directory where the error log is?

    Thanks

    Chris

    ReplyDelete
  9. Yes, the SQL Server service has full rights to ...\MSSQL\LOG folder. When I run EXEC sp_cycle_errorlog from SSMS, everything work ok. I think I'll run Sysinternals Process Monitor to find out what process is locking ERRORLOG when SQL is trying to rename it to ERRORLOG.1

    Thanks Chris

    ReplyDelete
  10. worth a go, let me know how you get on.

    Cheers

    Chris

    ReplyDelete