Filtering data from SQL*Server Errorlogs


There was an interesting question on Twitter that got bumped up:

How to filter line + following line using Get-SqlErrorLog? Something like Select-String -Context 1

From the PowerShell help:

The Context parameter captures the specified number of lines before and after the line with the match. This allows you to view the match in context.

If you enter one number as the value of this parameter, that number determines the number of lines captured before and after the match. If you enter two numbers as the value, the first number determines the number of lines before the match and the second number determines the number of lines after the match.

I was not sure if Get-SqlErrorLog supported a parameter like Context. So, I decided to check if we can do something like this, without too much code.

Add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.ErrorLogPath

Output is something like so, on my machine:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

From here it is pretty straight forward:

Get-ChildItem -Path $server.ErrorLogPath -Filter ERRORLOG* |
ForEach-Object -Process { Select-String -Path $_ -Pattern "Starting up database 'sqlchow'" -Context 1}

The output is something like:

  C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG:54:2013-08-17 08:33:58.26 spid52
Using 'dbghelp.dll' version '4.0.5'
> C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG:55:2013-08-17 12:55:59.64 spid53
Starting up database 'sqlchow'.
  C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG:56:2013-08-17 12:56:01.78 spid53
Setting database option COMPATIBILITY_LEVEL to 100 for database sqlchow.

Note: If you want to search only in the first error-log file then, just remove change the code to:

Get-ChildItem -Path $server.ErrorLogPath -Filter ERRORLOG |
ForEach-Object -Process { Select-String -Path $_ -Pattern "Starting up database 'sqlchow'" -Context 1}

Interesting stuff eh!

Advertisements
About

By profession, I’m a SQL Server Database Administrator. I love to poke my nose into different corners and see how stuff looks in there. I keep looking for new things to do as my mind refuses to settle on one topic.

Tagged with: , , , , , , ,
Posted in Fun-Stuff, PowerShell, SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: