One of the most useful logs you can review when there’s a problem in SQL Server is the ERRORLOG. It may not always be the answer to your problem, but it’s a good place to start.
When you initially install SQL Server it only keeps the most recent six (6) ERRORLOG files, in addition to the current, active one. A new ERRORLOG file is generated when the instance restarts, or when you run sp_cycle_errorlog. There are drawbacks to this default configuration. If you do not regularly restart your instance (which is perfectly fine), then one ERRORLOG file could contain months, maybe even a year or more, of information. That’s a lot of entries to read through if you’re looking for patterns or unusual errors. In addition, if you happen to run into a scenario where you restart the instance multiple times in succession – three or four times for example – you could potentially lose months of history.
The solution is to recycle the ERRORLOG on a regular basis (I like to do this weekly), and increase the number of files retained. To recycle the ERRORLOG every week, set up an Agent job that calls sp_cycle_errorlog. I’ve included code at the end of this post to create the Agent job and weekly schedule.
Next, increase the number of ERRORLOG files you keep. You can do this through Management Studio. Expand the instance, then Management, right-click on SQL Server Logs and select Configure. Enable the option Limit the number of error log files before they are recycled and then enter a number for Maximum number of error log files: I like to keep 30 around. That usually equates to about six months of time, including a few unplanned restarts.
You can also make this change with T-SQL:
USE [master]; GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30; GO
Checking the ERRORLOG configuration is always something we do as part of a health audit, and I’m always happy when I find systems with at least a few months’ worth of files that are less than a few MB in size (I think the largest ERRORLOG I’ve seen is 4GB…that one took a long time to open). If this isn’t something you’ve configured on your SQL Server instances yet, take a few minutes and knock it out. You won’t regret having this information when a problem comes up, or when you’re looking to see if a problem occurred a few months ago but maybe no one realized it.
If you’re interested in other posts in our SQLskills SQL101 series, check out SQLskills.com/help/SQL101.
Code to create a SQL Agent job to run sp_cycle_errorlog weekly (Sundays at 12:01 AM):
USE [msdb]; GO /****** Object: Job [SQLskills Cycle ERRORLOG Weekly] ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [Database Maintenance] ******/ 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'SQLskills Cycle ERRORLOG Weekly', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Cycle the ERRORLOG once a week.', @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 ERRORLOG] PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cycle ERRORLOG', @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; GO', @database_name=N'msdb', @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'Weekly cycle of ERRORLOG', @enabled=1, @freq_type=8, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20170301, @active_end_date=99991231, @active_start_time=100, @active_end_time=235959, @schedule_uid=N'23a32e3e-c803-451f-b85a-b77d5b97ab3a' 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