Change the number of SQL Error Logs using Powershell

One of the first places to start troubleshooting a potential SQL problem is to look at the SQL Error Logs. If there is a serious problem, it will normally show up in this log. The thing is, the default setting for SQL is to keep 6 logs. Each log is created when SQL Server starts up. So if a SQL server is restarting often, which is already a problem, you could have very little SQL error log history. On the other hand if SQL stays up for weeks or months at a time, which is a good thing, you will potentially have a very big error log to sift through to find any errors.

I recommend configuring 31 log files. This number can be anything from 6 to 99. I choose 31 because that will give me a months worth of error logs. How so if the error logs only get created when SQL Server restarts? I'll cover that in a subsequent blog.

But first, actually changing the number of error logs. One could obviously do it in Enterprise Manager or SQL Management Studio. But in my case I started at a client with 151 SQL servers of differing versions. Click click, click click. Very tedious. You guessed it, Powershell! :-)

What I like to do is create functions that encapsulate the unit of work that I want to accomplish. Once I have this function I can easily and neatly deploy this unit of work to a list of servers.

The function to change the number of SQL Error Logs is quite straighforward. It uses the Reg command to add the NumErrorLogs value to a certain registry key. There is actually no way to do this through T-SQL, unless I use xp_regwrite extended stored procedure. But that would entail connecting to SQL to write a value to the registry. Not necessary. I actually do connect to SQL, but only to determine the version of SQL. This I guess I could also do in the registry, but in my function I've connected to SQL using SMO.

So lets look at some code. Following is the function code:

function Change-ErrorLogs ($s)
{
$svr = get-sqlserver $s

if (($svr.get_information().version.major) -eq 8) 
{
#SQL 2000
reg add \\$s\HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer /v NumErrorLogs /t REG_DWORD /d 31
write-host "SQL 2000 Server - $s Config changed"
}
else
{
#SQL 2005
reg add "\\$s\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer" /v NumErrorLogs /t REG_DWORD /d 31
write-host "SQL 2005 Server - $s Config changed"
}
}

The code for get-sqlserver is:

function Get-SqlServer
{
    param([string]$sqlserver=$(throw 'Get-SqlServer:`$sqlserver is required.'))
    #When $sqlserver passed in from the SMO Name property, brackets
    #are automatically inserted which then need to be removed
    $sqlserver = $sqlserver -replace "\[|\]"

    Write-Verbose "Get-SqlServer $sqlserver"
    $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sqlserver
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject")
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject")
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.UserDefinedFunction], "IsSystemObject")
    #trap { "Check $SqlServer Name"; continue} $server.ConnectionContext.Connect() 
    return $server
    
} #Get-SqlServer

Great, so now I have my unit of work defined as Change-ErrorLogs. You can probably guess the rest. But if you can't, here is the code:

$ss = gc 'servers2.txt'
foreach ($s in $ss) {Change-ErrorLogs $s}

Servers2.txt contains a list of all my SQL Servers. Grab that list into $ss. And then for each server ($s) in the list ($ss), Change the number of error logs (Change-ErrorLogs). Tada!