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.
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.
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.
Root 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.
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
Leave a Comment