Interesting Text Parsing

At work this week we were getting a lot of SQL timeout errors.  We went to the DBA and he sends us a log file and explains that many of our sprocs are blocking resources.  He gave us this monster log file and left it up to us to get what we needed out of it.

Here is the basic syntax of the log file.

2006-11-17 19:03:20,733 WARNING BLOCKER -- MYDBSERVER SPID: 120, hostname MYWEBSERVER, prg .Net SqlClient Data Provider, ntlogin
2006-11-17 19:03:20,733 WARNING Blocking Buffer info: sp_executesql;1
--

This pattern was repeated ~600 times.  The log file reported on all the databases on the server, not just ours.  So, I came up with this PowerShell script to give me a list of unique stored procedures that caused a block on the SQL server.

$a = gc "blocks.txt"; 0..$a.count | % { if($a[[int]$_] -match "MYWEBSERVER") { ([regex]"ap\w*").match($a[([int]$_ + 1)]) } } | % { $_.Value } | sort -uniq

Yes, that is one line.  Thank you, PowerShell team, for giving us the ";" character.  ;-) 

The first line loads all the lines in the file into an array.

$a = gc "blocks.txt";

Then we loop through all the rows and pick out the lines that reference MYWEBSERVER.    NOTE: calling $a[$_] didn't work for me.  I had to cast $_ to an int, like so $a[[int]$_].

If the line contains the name of MYWEBSERVER, then I look at the next line and return everything that matches my regex expression, "ap\w*".  All of our sprocs have been prefixed with "ap" so this was pretty easy. 

0..$a.count | % { if($a[[int]$_] -match "MYWEBSERVER") { ([regex]"ap\w*").match($a[([int]$_ + 1)]) } }

The last step is to filter our result set to only pass through unique values.

sort -uniq

It took me about 15 minutes to write and test this.  However, next time we get a log like this, I'll be ready!