How to intelligently auto-cycle ERRORLOG using T-SQL

In my earlier post, I described different ways to read very large SQL Server Log (ERRORLOG) – 5GB – to be exact. However, this was a reaction to something that has already happened. In this post, I will show you how to prevent this from happening at all.

The Log

In case you wonder what the SQL Server Log is:SQL Server Log

Concept

Recycling log means renaming the existing file and creating a new, empty file in place. This is a common practice across different computer systems. In the case of the SQL Server Log file, the renaming involves adding a numerical suffix to the existing file behind the scenes:

ERRORLOG.1
ERRORLOG.2
ERRORLOG.3

whilst the current file has simply no suffix:

ERRORLOG

By default, cycling ERRORLOG happens upon the SQL Server restart. Production servers, however, can stay online without a restart for weeks or months and in some cases years and we need a way to keep the ERRORLOG files relatively small and manageable without having to restart the server.

Thankfully SQL Servers comes with a stored procedure to handle this:

sp_cycle_errorlog

Therefore, the easiest and most obvious way to execute log retention is to create a scheduled Agent job to execute sp_cycle_errorlog but this brings a few challenges:

  • in busy environments, daily rotation may not be enough (as shown in my earlier post)
  • on idle servers, daily rotation may be too frequent creating lots of files without much information
  • in a large estate, we may struggle to find that balance and could end up with different schedules adding complexity to maintenance

Dynamic recycling

The perfect approach would be a process that can detect both: file size and age and trigger cycling dynamically, based on those parameters. There are many ways to do this in either T-SQL and PowerShell. Below is my approach to achieving this in T-SQL.

Firstly, we need to get size and age information for existing log files. This can be done using the sp_enumerrorlogs ERRORLOG enumerator.

A word of caution, this procedure is undocumented. This means Microsoft does not want you to use it. There could be unforeseen consequences whilst using it, such as high CPU utilisation or unexplained failures. But it’s not all that bad, I will further below.
DECLARE @table_error_logs TABLE (
  log_number tinyint, log_date DATETIME, 
  log_bytes int
) INSERT into @table_error_logs (log_number, log_date, log_bytes) 
EXEC master.dbo.sp_enumerrorlogs

Once we have the sizing and the age, we can do simple calculations and conditional recycling:

select @ERRORLOG_bytes = log_bytes 
from @table_error_logs 
where log_number = 0 --current log to get size

select @ERRORLOG_age_hours = datediff( hour, log_date, getdate() ), @ERRORLOG_date = log_date 
from @table_error_logs 
where 
  /* 0 based array. Current log = 0 and previous log = 1. To calculate age of the current log we need to know when the old log was last written to. The log_date is simply a timestamp of when the log was last written to in case of the current log it will be constantly increasing until its recycled and no more information written to it */
  log_number = 1   

if ( @ERRORLOG_bytes > @ERRORLOG_max_size_bytes ) 
  or ( @ERRORLOG_max_age_hours > @ERRORLOG_max_age_hours ) 
    begin 
        --recycling
  	end 
else 
    begin 
        --not recycling
  	end

The full script:

USE [master] 
GO 

IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_auto_cycle_errorlog') 
    EXEC ( 'CREATE PROC [dbo].[sp_auto_cycle_errorlog] AS SELECT ''placeholder''') 

GO 

ALTER PROCEDURE [dbo].[sp_auto_cycle_errorlog] (
  @ERRORLOG_max_size_bytes INT = 104857600 -- 100MB, 
  @ERRORLOG_max_age_hours INT = 168 -- 7 days
  ) AS 
SET NOCOUNT ON;
/* 
  Marcin Gminski; https://github.com/marcingminski
      
  paremeters:
        @ERRORLOG_max_size_bytes INT  - file size in bytes, when ERRORLOG is bigger than that it will be cycled
        @ERRORLOG_max_age_hours INT   - file age in hours, when ERRORLOG is older than that it will be cycled
      
  Schedule this hourly and set size and age to your liking or leave the defaults of 100MB and 7 days 
*/

DECLARE @table_error_logs TABLE (log_number tinyint, log_date DATETIME, log_bytes int) 
DECLARE @ERRORLOG_bytes BIGINT 
DECLARE @ERRORLOG_age_hours BIGINT 
DECLARE @ERRORLOG_age_formatted VARCHAR(100) 
DECLARE @ERRORLOG_date DATETIME 

INSERT into @table_error_logs (log_number, log_date, log_bytes) 
EXEC master.dbo.sp_enumerrorlogs 

select @ERRORLOG_bytes = log_bytes 
from @table_error_logs 
where log_number = 0 --current log to get size

select @ERRORLOG_age_hours = datediff( hour, log_date, getdate() ), @ERRORLOG_date = log_date 
from @table_error_logs 
where log_number = 1 

set @ERRORLOG_age_formatted = right( '' + convert( varchar(10), datediff( hour, @ERRORLOG_date, getdate() ) / 24 ), 2 ) + ' ' + right( '00' + convert( varchar(10),  datediff( hour, @ERRORLOG_date, getdate() ) % 24 ), 2 ) + ':' + right( '00' + convert( varchar(10), datediff( minute, @ERRORLOG_date, getdate() ) % 60 ), 2 ) + ':' + right( '00' + convert( varchar(10),  datediff( second, @ERRORLOG_date, getdate() ) % 60 ), 2 ) 

if ( @ERRORLOG_bytes > @ERRORLOG_max_size_bytes ) or ( @ERRORLOG_max_age_hours > @ERRORLOG_max_age_hours ) 
    begin --recycling!
  		print 'ERRORLOG WILL BE RECYCLED.
    		MBytes: ' + convert( varchar(100), convert( decimal(5, 2), @ERRORLOG_bytes / 1024.0 / 1024.0 ) ) + ', Date created: ' + convert( varchar(23), @ERRORLOG_date, 121 ) + ', Age (dd HH:mm:ss): ' + @ERRORLOG_age_formatted 

        exec sp_cycle_errorlog 
    end 
else 
    begin 
        --not recycling
  		print 'ERRORLOG WILL NOT BE RECYCLED.
    		MBytes: ' + convert( varchar(100), convert( decimal(5, 2), @ERRORLOG_bytes / 1024.0 / 1024.0 ) ) + ', Date created: ' + convert( varchar(23), @ERRORLOG_date, 121 ) + ', Age (dd HH:mm:ss): ' + @ERRORLOG_age_formatted 
    end

It will generate the following output upon execution:

ERRORLOG WILL BE RECYCLED.
    MBytes: 109.37,
    Date created: 2018-09-31 20:59:00.000,
    Age (dd HH:mm:ss): 0 17:00:58

or, alternatively:

ERRORLOG WILL NOT BE RECYCLED.
    MBytes: 0.00,
    Date created: 2018-10-01 18:22:00.000,
    Age (dd HH:mm:ss): 0 00:01:45:

The sp_enumerrorlogs

Earlier in this post, I mentioned the undocumented sp_enumerrorlogs. Whilst the procedure is undocumented, it is, in fact, exactly what SSMS is using to produce a list of Log Files. When we open the SQL Log Viewer, this is what we can capture in the profiler:

SQL Server Log sp_enumerrorlogs

So I think it is not all that bad despite it being undocumented.

Conclusion

Despite the undocumented procedure, I have used this approach for several years, in many production environments on a number of servers without a single glitch. It’s a good way to keep up log retention on very busy and idle servers with minimum intervention.

Thank you for reading!

This site, like most websites, uses cookies. By continuing to use this website, you agree to their use.