Refreshing SQL Server Error Logs

Often the first stop in troubleshooting is taking a peek at those logs. SQL Server Error logs can provide a lot of useful information and when it comes down to troubleshooting a SQL Server problem one cannot go wrong by cracking open the log files.

The SQL Server error log by design does not recycle automatically unless the SQL Server service restarts. If we have a SQL Server that has been running without any normal operating issues for 3 months, there will be 3 months of log file data stored in a single log file.

There are two major concerns here, one is that the file will continue to grow causing disk space contention and second the larger the file is the less likely you are going to open it and get any results. Much like opening a 100+ MB text file in notepad, it is just not going to work.

There is an included stored procedure that comes with SQL Server, sp_cycle_errorlog. This stored procedure causes the current log to become archived and then creates a new error log. Generally it is a good idea to create a SQL Agent job to execute this stored procedure nightly at midnight.

Another setting that should be included is the maximum number of log files to keep. By default, that number is set to 6. If the log is recycled nightly only seven error logs will be present, six archives and the current log.

1.       Using SQL Server Management Studio (SSMS) connect to the database instance

2.       Expand the Management Tree node and right click on the SQL Server Logs node

3.       Select Configure from the context menu

4.       Select “Limit the number of error log files  before they are recycled”

5.       Set the “Maximum number of error log files” to an appropriate value for your organization

6.       Click OK

Use the code below to create the job.

USE [msdb]

GO

/****** Object:  Job [Cycle Error Logs]    Script Date: 1/5/2015 1:45:50 PM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 1/5/2015 1:45:50 PM ******/

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'Cycle Error Logs',

                                @enabled=1,

                                @notify_level_eventlog=0,

                                @notify_level_email=0,

                                @notify_level_netsend=0,

                                @notify_level_page=0,

                                @delete_level=0,

                                @description=N'Cycles Error Logs Nightly',

                                @category_name=N'[Uncategorized (Local)]',

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

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

/****** Object:  Step [Step 1]    Script Date: 1/5/2015 1:45:50 PM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1',

                                @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'Run At Midnight',

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

                                @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

TAGS