THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

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 really fun to find gems like this that make our lives as a DBA much easier.

Allen

Published Tuesday, September 25, 2012 3:33 PM by AllenMWhite

Attachment(s): scan-errorlog.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ronald Dameron said:

Hi. I think you should say that PowerShell v2 introduced an "updated" Select-String cmdlet instead of a new cmdlet.  According to Bruce Payette's books, Select-String was a v1 cmdlet.  For PowerShell v2 it was updated to include several additional parameters to make it far more useful. The -context paramater is one of them.

Looking forward to your 2012 PASS Summit pre-con!

September 25, 2012 3:52 PM
 

AllenMWhite said:

You're absolutely right, Ronald. It does work with sqlps.exe, so I removed that comment. Thanks for helping out! See you in November.

September 25, 2012 4:44 PM
 

Ana Mihalj said:

Thanks for the post Allen.

We can concatenate the properties inside -inputobject. This code works for me.

$err | Select-String -inputobject {[string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text} -pattern 'Error:' -context 0,1

September 26, 2012 9:31 AM
 

AllenMWhite said:

Thanks, Ana! That makes so much sense once I see it, but it didn't occur to me as I was building the script.

September 26, 2012 10:17 AM
 

Pats said:

I don't know why it returns without any results when i give something like ./scan-errorlog.ps1 ServerName 09/27/2012 DBCC. When i look at the SQL Server log, it has DBCC logs. Any thoughts?

September 28, 2012 2:40 PM
 

Pats said:

I got answer to my question. The script above is trying to search for current log ReadErrorLog() and the DBCC messages which i'm searching for are stored in a previous log... I tried changing it to $err = $s.ReadErrorLog(0) + $s.ReadErrorLog(1) to grab current and earlier log and it returns the result. Thanks for your awesome script Allen.

September 28, 2012 4:22 PM
 

Chad Miller said:

I accept your challenge, sir :)

Here's another way of doing this by using the linenumber property returned from select-string. The linenumber property is a 1-based index, while the err variable is a Datatable which consists of datarows which is 0-based index. LineNumber -1 is row which matches and linenumber is the \next row. You can also provide an array as input to the pattern parameter.

$svr = new-object ('Microsoft.SqlServer.Management.SMO.Server') "MyServer"

$err = $svr.ReadErrorLog()

$err | Select-String -inputobject {$_.Text} -pattern 'Error: ','DBCC' |

foreach { $err.Rows[$_.LineNumber - 1] |

add-member PostText $(if ($_.Pattern -eq 'Error: ') {$err.Rows[$_.LineNumber].Text}) -PassThru -force }

September 30, 2012 9:26 AM
 

sql_noob said:

out of curiosity, how do you run this? do you run it on your workstation and just read the results? do you save them or email to yourself?

i have vbscripts running on all the servers and using logparser to dump the data into a database and then SSRS to send the reports daily.

October 1, 2012 3:19 PM
 

AllenMWhite said:

Chad, I knew I could count on you! Thanks!

sql_noob, I run this manually right now, but it'd be easy to assign the results to a variable and then make that variable the body of an email message, sent via SMTP, like this:

$email = New-Object Net.Mail.SmtpClient($smtpsrv)

$email.Send($from,$to,$subject,$body)

Allen

October 1, 2012 3:28 PM
 

KKline said:

Great article, Allen!

Do you have any comparative performance feedback on this approach?  For example, I might've looked at this in the past using SMO (or as close as I could get) or a linked server query against sys.dm_os_performance counters.

Is this faster?  I'm sure it has other benefits too (security, maybe?) but I don't know PowerShell well enough to guess what those benefits might be.

-Kev

October 15, 2012 8:18 PM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement