Increasing the Number of SQL Server Error Logs – My Automation Journey – Part 1

  • Article

I recently ran into an issue (will describe it in a follow-up post) in which I needed to review SQL Server Error logs after a weekend of server patching. It turns out the server was rebooted several times during the maintenance window, so when I went in to look at the SQL Server Logs, I saw something like this:

I was really needing to look at logs from October 8th which, as you can see, had rolled over. Most (if not all) events that SQL Server writes to its error logs are also written to the Windows Event Logs (Application Log to be exact). In my case, I got lucky, and the Windows Application logs were still intact, but what if the Windows Application log cycles through or is cleared by the OS team outside the control of the SQL team? So, my thoughts turned to how do I increase the number of logs to ensure I have sufficient logs in the future? This two-part post will walk through my journey to automate this setting across a large environment.

By default, SQL Server will keep 7 logs (6 archive and 1 current). You can configure up to 99 archive logs. See the screen prints below on how to do this.

Right click on SQL Server Logs and select Configure

Set the value and click OK.

This is great! What if I now want to apply this setting to ALL my instances (say I have 100 or more)? The GUI is great but not a scalable solution. If you click Script, it will generate a SQL script that looks something like this:

 
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10
GO

I've never used xp_instance_regwrite before, so I decided to dig into it a little more. You'll find very little if any documentation on this xp since it is technically undocumented. The gist of the xp is that it will replace the highlighted MSSQLServer in 'Software\Microsoft\MSSQLServer\MSSQLServer' with the path to the instance. In my case that would be 'Microsoft SQL Server\MSSQL13.SQL2016' – SQL 2016 with a named instance 'SQL2016'. In a nutshell, SQL Server reads the value NumErrorLogs from 'HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL1X.INSTANCENAME\MSSQLServer\' to determine the number of error logs to keep.

That completes part 1 of this post. In the next post, I will walk through a PowerShell script that will allow you to automate the setting of this value.

Comments

  • Anonymous
    November 06, 2016
    I use PowerShell SMO to do this $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $server$srv.Settings.NumberOfLogFiles = $Number$srv.Alter()I have placed the script on the gallery as well https://www.powershellgallery.com/packages/set-sqllogfiles/1.0/DisplayScript
  • Anonymous
    November 11, 2016
    we have a totally automated installation - below is how we perform the increase:----------------------------------------------------------------------------------- set the maximum number of SQL errorlogs to 99-------------------------------------------------------------------------------- exec xp_instance_regwrite 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\MSSQLServer\MSSQLServer', 'NumErrorLogs', REG_DWORD, 99 go