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=enWindows Installer 4.5
Redistributablehttp://www.microsoft.com/downloads/details.aspx?familyid=5A58B56F-60B6-4412-95B9-54D056D6F9F4&displaylang=enMicrosoft .NET Framework 3.5
Service Pack 1 (Full Package)http://download.microsoft.com/download/2/0/e/20e90413-712f-438c-988e-fdaa79a8ac3d/dotnetfx35.exeMicrosoft SQL Server System CLR
Types x86:http://go.microsoft.com/fwlink/?LinkId=123721&clcid=0x409Microsoft SQL Server 2008
Management Objects x86:http://go.microsoft.com/fwlink/?LinkId=123708&clcid=0x409This 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:
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.