Errorlog Configuration Problem When Upgrading SQL Server

Problem:

This morning when working with one of my SQL Server servers, I went looking for an error log from last August.  After finding that I was only able to go back six days, I checked the directory that stored SQL Server’s error logs,  and noticed a gap in the logs from October to last March.

Errorlog Directory

When I checked the configuration for the error logs in SQL Server Management Studio, I saw that the setting was configured at the default setting of 6 error logs for history.

default setting of 6 error logs

When I joined my company, I changed it’s standard for SQL Server to keep 99 error logs for history, and have a scheduled job that rotates the error log once a day.

-- Set Error logs parameter to 99

      USE [master]
      GO

      EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
             ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
             ,N'NumErrorLogs'
             ,REG_DWORD
             ,99
      GO

However, when I looked at the registry entry for that SQL Server server, I saw that the registry key “NumErrorLogs” was missing.

registry key “NumErrorLogs” was missingRoot Cause:

The registry key “NumErrorLogs”  is configured in the registry folder for the version SQL Server is running.  When I upgrade this SQL Server from version 2014 to 2016 last March, the registry folder MSSQL12.MSSQLSERVER was replaced with the new registry folder MSSQL13.MSSQLSERVER, and the registry key NumErrorLogs was not moved.

registry key NumErrorLogs

Resolution:

Just re-run the following script after the upgrade.  This step now has been added to my company’s procedures for upgrading SQL Server.

-- Set Error logs parameter to 99

      USE [master]
      GO

      EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
             ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
             ,N'NumErrorLogs'
             ,REG_DWORD
             ,99
      GO

Best Practices Standard for Error Logs

Leave a Reply