Scanning the Error Log with PowerShell

One of the most important things you can do as a DBA is to keep tabs on the errors reported in the error log, but there’s a lot of information there and sometimes it’s hard to find the ‘good stuff’. You can open the errorlog file directly in a text editor and search for errors but that gets tedious, and string searches generally return just the lines with the error message numbers, and in the error log the real information you want is in the line after that.

PowerShell 2.0 introduced a new cmdlet called Select-String which searches through a text file and returns the lines matching the target string. What it adds is an command-line parameter [-context] which allows you to specify the number of lines before and after the match is found. Bingo! By specifying the parameter -context 0,1 with the Select-String cmdlet it returns the line with the error, and the line afterwards.

To see what this does, first navigate to the errorlog directory, then issue the following commands:

$errlog = Get-Content '.\ERRORLOG'
$errlog | Select-String  -pattern 'Error:' -context 0,1

Now that’s really useful, but it does mean that I have to log on to each server to get the errors, or I have to somehow know where every server’s errorlog directory is and connect through the network to get to the files. Not so nice if you want to work from a client and ‘just get the errors’.

Using SMO, there’s a method under the Server object called ReadErrorLog() which reads the error log and (if assigned to a variable) creates a DataRow object containing the LogDate, ProcessInfo and Text properties from the errorlog. The ProcessInfo property contains the spid or other identifying value to indicate the source of the problem. By connecting to an instance using the Server object and loading the error log into the DataRow object, we can then get similar results, but we have to add an additional parameter, -inputobject, which lets us specify the property to search for our string.

$svr = new-object ('Microsoft.SqlServer.Management.SMO.Server') MyServer
$err = $svr.ReadErrorLog()
$err | Select-String -inputobject {$_.Text} -pattern 'Error:' -context 0,1

The advantage of this solution is that I don’t log into the target server and I don’t have to know where the log file is. The downside is that we lose the LogDate and ProcessInfo properties altogether, and there’s no way to piece the parts back together. (Now, as sure as I say this Chad Miller will pipe up with a cleaner way to do this. This is good, and it’s how I learn.)

What I found that works, though it’s a tad clunky for my taste, is to concatenate the properties back together before sending it to the Select-String cmdlet, like this:

$errlog = @()
$err | foreach { $errlog += [string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text }

The problem with this approach, of course, is you have to pass through the errorlog twice. To minimize this, I added an option to only report the errors starting with a particular date.

$errlog = @()
$err | where {$_.LogDate -ge $startdt} | foreach {
	$errlog += [string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text
	}

This minimizes the issue, and if you’re properly cycling the error log (normally once a week) the amount of log data it has to cycle through twice isn’t too bad. With the properties concatenated we then return to the original search command to get our results.

$errlog | Select-String  -pattern 'Error:' -context 0,1

But wait, there’s more! After playing with it for a bit I realized I’d like an option to just return DBCC results. Unlike errors, DBCC results are reported on the same line as the DBCC reports it was run, so I don’t need the line afterward. In my script I added a variable called $srch to the command line arguments, and if it’s equal to ‘DBCC’ it just returns the DBCC lines.

# Search the errorlog and return any error and the subsequent detailed message
if ($srch -eq 'DBCC') {
	$errlog | select-string -pattern 'DBCC' -context 0,0
	}
else {
	$errlog | select-string -pattern 'Error:' -context 0,1
	}

And that’s it! I named the script scan-errorlog.ps1, and it takes the instance name, start date and (optionally) ‘DBCC’ as parameters, and returns either errors for that server after the start date, or DBCC results since the start date.

./scan-errorlog.ps1 MyServer 9/25/2012

That command line will return any errors reported in the errorlog since midnight last night.

It’s important to note that this will not work using the sqlps.exe included with SQL Server 2008 or SQL Server 2008 R2, as the cmdlet is supported for PowerShell 2.0 and above. That said, it’s really fun to find gems like this that make our lives as a DBA much easier.

Allen

(This blog was originally posted on September 25, 2012.)

Update 11/25/2019: I’d neglected to make the script reachable after the move from sqlblog.com (which has since been taken down). Here it is. scan-errorlog.zip. AW.