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 had 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:

Concept

Recycling a log means renaming the existing file and creating a new, empty one. 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. We need to keep the ERRORLOG files relatively small and manageable without restarting the server.

Thankfully, SQL Servers come 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 or 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.

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 explain 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 what SSMS uses to produce a list of Log Files. When we open the SQL Log Viewer, this is what we can capture in the profiler:

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 several servers without a glitch. It’s an excellent way to maintain log retention on busy and idle servers with minimum intervention.

Thank you for reading!