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:

whilst the current file has simply no suffix:

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:

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 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 achieve this in T-SQL.

Firstly, we need to obtain 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.

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

The full script:

It will generate the following output upon execution:

or, alternatively:

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 list of Log Files. When we open 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 a 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.