Best practices for the SQL Server ERRORLOG  

SQL Server Error logs not optimally

What’s the SQL Server error log?

It’s a file used by SQL server to write data about events that occurred as backups, stack dumps, and a lot of other informational events.

Why should you care about it?

When the SQLError log gets too big, it’s harder to open them up. Therefore, you want the size to be kept smaller than bigger.

By the way, this check is a part of our SQL Server Health Check.

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

Troubleshooting is crippled.

Having not enough error log files (configured to keep 7 log files, max available is 99) makes troubleshooting more difficult. Some data patterns cannot be found.

If you hit a problem, which requires multiple SQL Engine restarts or reboots, every time you do that, SQL will create a new ErrorLog file.

Since you only setup to keep six only, the oldest one gets deleted, which it can quickly delete files we needed for investigating or troubleshooting purposes.

How to fix it?

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

Set up to keep 99 SQL error log files.

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

    Show the location of SQL server logs in the SSMS.
    Figure 1 – SSMS – SQL Server Logs.
  3. Enable Limit the number of error log files before they are recycled and then enter a value for the maximum number of error log files.

    Shows the checkbox that limits the number of error log files in the SQL Server.
    Figure 2 – Set Maximum number of error log files (I like to keep 99, max available).

Create a SQL Agent job to recycle the ErrorLog

Rotate Error Log every 24h or every 7days that way each day/week new file is created (best practices recommend to do this weekly).

You can use the script below to create the SQL Server Agent JOB.

  1. It will create a Job name ‘_Maint – SQL Error Log Cycle’. Alter the variable @Job_name if you wanna change it.
  2. The script will create a schedule to run nightly (Every Monday at 12:00AM).  You can change the parameters for sp_add_jobschedule and schedule it to another date.
  1. USE [msdb]
  2. GO
  4. DECLARE @ReturnCode INT
  5. SELECT @ReturnCode = 0
  6. IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
  7. BEGIN
  8. EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
  9. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  10. END
  11. DECLARE @jobId BINARY(16)
  12. EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'_Maint - SQL Error Log Cycle',
  13.         @enabled=1,
  14.         @notify_level_eventlog=0,
  15.         @notify_level_email=0,
  16.         @notify_level_netsend=0,
  17.         @notify_level_page=0,
  18.         @delete_level=0,
  19.         @description=N'Cycle SQL Error Log (daily), so files don''t grow too large.',
  20.         @category_name=N'[Uncategorized (Local)]',
  21.         @owner_login_name=N'sa', @job_id = @jobId OUTPUT
  22. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  23. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cycle SQLError log',
  24.         @step_id=1,
  25.         @cmdexec_success_code=0,
  26.         @on_success_action=1,
  27.         @on_success_step_id=0,
  28.         @on_fail_action=2,
  29.         @on_fail_step_id=0,
  30.         @retry_attempts=0,
  31.         @retry_interval=0,
  32.         @os_run_priority=0, @subsystem=N'TSQL',
  33.         @command=N'EXEC sp_cycle_errorlog;',
  34.         @database_name=N'master',
  35.         @flags=0
  36. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  37. EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
  38. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  39. DECLARE @mydate varchar(8)
  40. SELECT @mydate = CONVERT(varchar(20), getdate(), 112)
  41. EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run nightly',
  42.         @enabled=1,
  43.         @freq_type=8,
  44.         @freq_interval=2,
  45.         @freq_recurrence_factor=1,  
  46.         @freq_subday_type=1,
  47.         @freq_subday_interval=0,
  48.         @freq_relative_interval=0,
  49.         @active_start_date=@mydate,
  50.         @active_end_date=99991231,
  51.         @active_start_time=0,
  52.         @active_end_time=235959--, @schedule_uid=N'ec6602b8-b42f-4ada-a9f5-5a72424206dc'
  53. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  54. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
  55. IF (@@ERROR <> 0 OR @ReturnCode <>; 0) GOTO QuitWithRollback
  57. GOTO EndSave
  58. QuitWithRollback:
  60. EndSave:
  61. GO

More information

Erin Stellato -SQLskills SQL101: The SQL Server ERRORLOG  

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *