Share via

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

  • Article

In my last post, I discussed how to increase the error logs through the GUI and explained the back end stored procedure that is used to set the value in the registry. In this post, I will cover a technique to automate this and dig a little into Windows Management Instrumentation (WMI).

As Rob and Rudy Komacsar pointed out in the comments section of my first post, there are a plethora of ways to automate this setting. The approach I took was a little different than theirs although their approaches are equally valid and effective at getting the job done – nice work and thanks to both of you for the tips!

For this post, the approach I took was to leverage WMI directly as it affords me the opportunity to easily segue into a discussion around WMI which is a powerful tool to manage SQL Server. There are other benefits and reasons I chose this route including the fact that it allows you to configure the setting without ever having to connect to the engine, and it can be applied to all instances and versions on the server in one fell swoop.

If you don't know what WMI is, here's a link to some background information. WMI is not only integral to Windows but is accessible through Windows via the Get-WMIObject cmdlet. That cmdlet allows you to retrieve an instance of a specific WMI class. Each WMI class belongs to a namespace which is essentially a path or a unit of scoping for a class. Let's get into some examples and see how SQL Server leverages WMI.

The namespace for the SQL Server WMI provider is root\microsoft\sqlserver\computermanagementxx where xx is the version of SQL Server (10 = 2008, 11=2012, 12=2014 and 13=2016). Before we get into code, there's an interesting tool you can use to enumerate the list of classes and instances of a WMI class. That tool is WBEMTest and is installed on all Windows machines. To run it, hit a command prompt, type wbemtest and enter.

Click Connect and enter the respective namespace (in my case - root\microsoft\sqlserver\computermanagement13) and click connect

Click Enum Classes

Click OK

This box shows the list of all classes available in this namespace. The class we are interested in is SqlServiceAdvancedProperty. Double click on it.

Within this class are three methods (SetBoolValue, SetNumericalValue and SetStringValue). There are several properties (IsReadOnly, PropertyIndex, etc.). This class provides a set of name/value pairs for the properties of each SQL Server service. If you click Instances, you can browse the actual data in the WMI repository. A great tool for just plain spelunking.

I've provided a PowerShell script that will dump out the data from that class for each instance/service/version of SQL Server on the local machine.

 
cls
$namespaceBase = "root\microsoft\sqlserver\computermanagement"

for ($index = 10; $index -le 14; $index++)
{
    $advProps = Get-WmiObject -Namespace ($namespaceBase + $index.ToString()) -Class "SqlServiceAdvancedProperty" -ErrorAction Ignore
 
    if ($advProps -ne $null)
    {
        Write-Output ("Found Properties Under: " + $namespaceBase + $index.ToString())
        foreach ($property in $advProps)
        {
            $output = ($property.ServiceName + " --> " + $property.PropertyName)
            
            # String Type
            if ($property.PropertyValueType -eq 0)
            {
                $output += (" = " + $property.PropertyStrValue)
            }
            
            #Boolean Type
            if ($property.PropertyValueType -eq 1)
            {
                $output += (" = " + [System.Boolean]$property.PropertyNumValue)
            }

            # Int Type
            if ($property.PropertyValueType -eq 2)
            {
                $output += (" = " + $property.PropertyNumValue)
            }
            
            $output
        }
    }
}

This script will output a lot of information about EVERY instance installed on the server. One Name/Value pair included in the output is REGROOT.

Note, this is the same path that is used/translated by xp_instance_regwrite. You just need to add in the extra \MSSQLServer at the end, and you are looking at the key where the NumErrorLogs value is located.

Now that you have the path, all you need to do it set the value to the number of error logs you want. An example of setting the value through PowerShell is shown below. This will set the value for all instances of SQL Server 2016.

 
$numLogs = 42 
$objects = Get-WmiObject -Namespace "root\microsoft\sqlserver\computermanagement13" -Class "SqlServiceAdvancedProperty" | where-object {$_.PropertyName -eq "REGROOT" -and $_.SqlServiceType -eq 1}
    foreach ($object in $objects)
    {
        Write-Output ("Setting " + ("HKLM:\" + $object.PropertyStrValue + "\MSSQLServer\NumErrorLogs") + " to " + $numLogs.ToString() + ".")
        Set-ItemProperty -Path ("HKLM:\" + $object.PropertyStrValue + "\MSSQLServer") -Name "NumErrorLogs" -Value $numLogs 
    }

If you are interested in digging into the other WMI classes provided by SQL Server, here is an MSDN link to that documentation.

I hope this helped open your eyes to the power of PowerShell and WMI for SQL Server Automation.

Comments