{"id":1859,"date":"2019-11-01T23:13:44","date_gmt":"2019-11-01T23:13:44","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=1859"},"modified":"2019-11-01T23:15:55","modified_gmt":"2019-11-01T23:15:55","slug":"errorlog-configuration-problem-when-upgrading-sql-server","status":"publish","type":"post","link":"http:\/\/www.theSQLReport.com\/?p=1859","title":{"rendered":"Errorlog Configuration Problem When Upgrading SQL Server"},"content":{"rendered":"

Problem:<\/b><\/p>\n

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

\"Errorlog<\/a><\/p>\n

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.<\/p>\n

\"default<\/a><\/p>\n

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.<\/p>\n

-- Set Error logs parameter to 99\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 USE [master]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 GO\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,N'Software\\Microsoft\\MSSQLServer\\MSSQLServer'\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,N'NumErrorLogs'\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,REG_DWORD\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,99\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 GO<\/pre>\n
However, when I looked at the registry entry for that SQL Server server, I saw that the registry key \u201cNumErrorLogs\u201d was missing.<\/p>\n
\"registry<\/a>Root Cause:<\/b><\/p>\n
The registry key \u201cNumErrorLogs\u201d \u00a0is configured in the registry folder for the version SQL Server is running.\u00a0 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.<\/p>\n
\"registry<\/a><\/p>\n
Resolution:<\/b><\/p>\n
Just re-run the following script after the upgrade.\u00a0 This step now has been added to my company’s procedures for upgrading SQL Server.<\/p>\n
-- Set Error logs parameter to 99\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 USE [master]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 GO\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,N'Software\\Microsoft\\MSSQLServer\\MSSQLServer'\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,N'NumErrorLogs'\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,REG_DWORD\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,99\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 GO<\/pre>\n
\"Best<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Problem: This morning when working with one of my SQL Server servers, I went looking for an error log from last August.\u00a0 After finding that I was only able to … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":1862,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[159,172,171,157],"class_list":["post-1859","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-errorlog","tag-numerrorlogs","tag-registry","tag-upgrade"],"_links":{"self":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1859"}],"collection":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1859"}],"version-history":[{"count":7,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1859\/revisions"}],"predecessor-version":[{"id":1871,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1859\/revisions\/1871"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/1862"}],"wp:attachment":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1859"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1859"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1859"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}