October 31, 2012 at 5:15 pm
Hi, is it possible to check previous blocking details, like who is the lead blocker, what is blocking.
I know how to check real time blocking by using sp_who2
is there any script available?
October 31, 2012 at 5:32 pm
Which version of SQL Server are you running?
October 31, 2012 at 5:37 pm
Microsoft SQL Server 2000 - 8.00.2187
and
MS SQL SERVER 2008 SP3
October 31, 2012 at 5:50 pm
SQL 2000 is a tougher one. Unless you aqcuire a tool that periodically collects the data, I know of no way to review blocking historically. In the past, I've written my own process that takes periodic snapshots of master..sysprocesses and loads the data to a series of tables. I was then able to review the data in the tables and trace the source of the blocking back to the head SPID.
SQL 2008 is significantly easier as you can use SQL Profiler and the blocked process report to view historical blocking.
Here is a link to an article on SQL Server Central that discusses how to set it up: http://qa.sqlservercentral.com/articles/Blocking/73148/
We run an automated server side trace that collects the data, then use a stored procedure that parses out the data into a table format for easier analysis. The original stored procedure can be found here: http://michaeljswart.com/2011/04/a-new-way-to-examine-blocked-process-reports/
I've customized it to add additional columns to the output and to parse out the blocked resource based on the OBJECT, PAGE, or KEY values.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply