Best Practices for the SQL Server ERRORLOG

Category: Performance

What Is the SQL Server Error Log?

This is a file used by the SQL Server engine to write data about events such as backups, dump events, and a lot of other informational events.

Why Should You Care About This?

When the SQL Error Log gets too large, it becomes harder to open. That’s why you want to keep its size small rather than large.

It’s easier for errors to “hide” in larger files.

This gets in the way of troubleshooting.

Not having enough error log files (configured to keep 7 log files, with a maximum available count of 99) makes troubleshooting harder. Some data patterns can’t be found.

If you run into an issue that requires restarting the SQL Engine multiple times, SQL creates a new Error Log file every time you do that.

Since you’ve only set it to keep six of them, the oldest one gets deleted, and files you might need for investigation or troubleshooting can quickly get purged.

How Do You Fix It?

The solution is to recycle the ERRORLOG regularly and increase the number of files retained.

Configure it to keep 99 SQL error log files.

Open Management Studio (SSMS).
Expand the instance, then go to Management, right-click SQL Server Logs, and select Configure.

3. Enable “Limit the number of error log files before they are recycled,” then enter a value for the maximum number of error log files.

Create a SQL Agent Job to Recycle the ErrorLog

Cycle the Error Log every 24 hours, or every 7 days, so a new file is created every day/week (best practices recommend doing this weekly).

SQL Server Consulting

Need expert support for SQL Server?

Our senior database team supports SQL Server performance tuning, health checks, migrations, Remote DBA services and urgent operational issues.

Explore Services

You can use the following script to create a SQL Server Agent Job example.

This will create a job named ‘_Maint – SQL Error Log Cycle’. Change the @Job_name variable if you want to rename it.
The script will create a schedule that runs overnight (every Monday at 12:00). You can change the parameters for sp_add_jobschedule to schedule it for a different date.

USE [msdb]

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'_Maint - SQL Error Log Cycle',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Cycle SQL Error Log (daily), so files don''t grow too large.',

@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'Cycle SQLError log',

@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

DECLARE @mydate varchar(8)

SELECT @mydate = CONVERT(varchar(20), getdate(), 112)

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run nightly',

@enabled=1,

@freq_type=8,

@freq_interval=2,

@freq_recurrence_factor=1,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@active_start_date=@mydate,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959--, @schedule_uid=N'ec6602b8-b42f-4ada-a9f5-5a72424206dc'

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

Leave Your Day-to-Day SQL Server Operations to Us

Aryasoft’s Remote DBA team continuously and proactively manages your maintenance plans, job/agent administration, and server operations.

Explore DBA Services

Best Practices for the SQL Server ERRORLOG

TABLE OF CONTENTS