THE SQL Server Blog Spot on the Web

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

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

New Codeplex Tool - SQL Blocked Process Monitor

Update: Bug Notice!!!

This app requires that you have .NET 3.5 installed and that you have the SQL Server 2008 SMO components installed for the alpha version to work. This was done by design, and is a shortcoming in my initial work that will be corrected in the next release, hopefully in the next 48 hrs. If you want to test the application, you can do so if you have .NET 3.5 installed and you install the SQLCLR Types and SQL SMO features from the SQL Server 2008 Feature Pack:
http://www.microsoft.com/downloads/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en

Windows Installer 4.5 Redistributable
http://www.microsoft.com/downloads/details.aspx?familyid=5A58B56F-60B6-4412-95B9-54D056D6F9F4&displaylang=en

Microsoft .NET Framework 3.5 Service Pack 1 (Full Package)
http://download.microsoft.com/download/2/0/e/20e90413-712f-438c-988e-fdaa79a8ac3d/dotnetfx35.exe

Microsoft SQL Server System CLR Types x86:
http://go.microsoft.com/fwlink/?LinkId=123721&clcid=0x409

Microsoft SQL Server 2008 Management Objects x86:
http://go.microsoft.com/fwlink/?LinkId=123708&clcid=0x409

This project will be split into a version for SQL Server 2008, which is what exists today, and another version targeting .NET 2.0 and SQL Server 2005, which will be coded correctly in the next release.

Original Post:

I just uploaded a new tool onto Codeplex for troubleshooting blocking in SQL Server 2005 and 2008.  SQL Server 2005 introduced the blocked process report, which is a XML document that is output in SQL Trace when a process is blocked for longer than the value specified in the Server Configuration option for the Blocked Process Report.  For more information on the Blocked Process Report, see the article I wrote for SQL Server Central last year:

Using the Blocked Process Report in SQL Server 2005/2008 ...

One of the downsides to the blocked process report is that it can be difficult to use on a busy server, and unless the sqltext is collected immediately, it may be gone by the time you are analyzing the trace output.  This is where the SQL Blocked Process Monitor comes into play, as it uses a SMO trace, and collects the blocked process reports from SQL Server, then uses the sqlhandle's in each report immediately to get the sqltext from sys.dm_exec_sql_text DMV.  It then adds a node under each frame for the sqlstatement and updates the XML document before writing it to a XML output file. 

The site for the tool is:

http://sqlblockedprocessmon.codeplex.com/

I have a few more options to add to the command line parameters, but this has been used on two of my servers with great success in the last few weeks.  If you happen to use the tool and have feedback, please let me know.

Published Monday, April 27, 2009 9:35 PM by Jonathan Kehayias

Comments

 

VicDBA said:

hey jim, downloaded your SQL BPM tool from codeplex, the .exe file is erroring out, am i missing something here! I am using SQL 2005 32bit Std ed on Win Server 2003 32 bit STD ed. Tweet me if you need further info!

April 28, 2009 10:47 AM
 

Dave said:

I just upgraded a server that has lots of blocking issues from SQL 2000 to 2008 and am looking for a way to get email notifications on blocks.  I found your code - which looks promising, but was updated with the updates promised above.  Can you tell me - can I use this for emailing block notifications, and if not - do you have a recommendation?

- Dave

December 9, 2009 12:52 PM
 

Jonathan Kehayias said:

Dave,

Shoot me a email through here and I'll show you how to solve this.

December 9, 2009 1:52 PM
Anonymous comments are disabled

This Blog

Syndication

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