April 9, 2010 at 3:40 pm
Thanks for the reply!
I already ran that script, but, as I said, the information returned is not reliable.
Example: in my blocked process report, it shows that the table A is executing an update and it's blocked and in the blocking handle show that a select on table B is being executed. It doesn't make any sense to me, since table A is not related at all to table B.
Is there another way to check exactly what is blocking?
April 9, 2010 at 3:45 pm
If there are transactions involved locks may be retained on tables from previous statements. Even though the second spid might be selecting from table b it may still have a lock of some sort on table a if this is wrapped within a transaction.
April 9, 2010 at 3:49 pm
That's what I'm thinking. Since it's inside a stored procedure, it might being blocked by a previous transaction. The problem is, how can I get the exact statement that is causing the lock?
April 10, 2010 at 12:14 pm
Any ideas?
April 12, 2010 at 7:49 am
Anyone?
October 14, 2010 at 10:03 am
Jon,
How about if I just want a normal blocked process report trace (without the XML). Would you have a template (or column settings and filter) you recommend?
October 14, 2010 at 10:34 am
Jonathan,
What about turning the blocked process threshold off when you are done?
August 10, 2011 at 6:26 am
how to read the data from blocked process threhold report? I have enabled blocked process threshold in one of my server. Can someone tell me how to do it?
January 4, 2012 at 8:13 am
manoj.ks (8/10/2011)
how to read the data from blocked process threhold report? I have enabled blocked process threshold in one of my server. Can someone tell me how to do it?
Use SQL Server Profiler and select the 'Block Process Report' event. Once blocking occurs, the Profiler will show it.
January 4, 2012 at 8:18 am
Jonathan,
I've received the below for the blocked process:
<inputbuf>
Proc [Database Id = 14 Object Id = 1311948665] </inputbuf>
</process>
This is a stored procedure that doesn't touch the table that was being blocked. Could this indicate that: the blocked process report only shows the last executed query of a multi-query batch or transaction?
How else can a table be blocked by a SP that has nothing to do with it?
January 4, 2012 at 8:22 am
Yes, the root of the blocking could be from a previous statement in a transaction.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
January 5, 2012 at 12:32 am
Jonathan Kehayias (1/4/2012)
Yes, the root of the blocking could be from a previous statement in a transaction.
Thanks. I again lost hope to capture the query that caused blocking in SQL Server. However, with the Blocked Process Report, I can at least identify the transaction responsible.
August 19, 2013 at 12:18 pm
don.schaeffer (4/9/2010)
This script might get what you want:select
WaitTime = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@waittime','varchar(20)' ),
BlockingQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocking-process/process/inputbuf)[1]','varchar(200)'),
BlockingSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@spid','varchar(20)' ),
BlockingLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@lastbatchstarted','varchar(20)' ),
BlockingSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ),
BlockingStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ),
BlockingStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' ),
BlockedQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocked-process/process/inputbuf)[1]','varchar(200)'),
BlockedSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@spid','varchar(20)' ),
BlockedLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@lastbatchstarted','varchar(20)' ),
BlockedSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ),
BlockedStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ),
BlockedStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' )
from dbo.Feb02 t
where eventclass = 137
order by BlockedLastBatchStarted
I'm doing something very similar to what you do here. I'm trying to take it one step further and derive the actual sql statement from the BlockingSQLHandle. When I take the handle and plug it in manually like so, all is great:
select * from sys.dm_exec_sql_text (0x03000d004884ee666fb67301b9a100000100000000000000),
but when I try to do it all in one step like the following I get an error:
select
WaitTime = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@waittime','varchar(20)' ),
BlockingQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocking-process/process/inputbuf)[1]','varchar(200)'),
BlockingSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@spid','varchar(20)' ),
BlockingLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@lastbatchstarted','varchar(20)' ),
BlockingSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ),
BlockingStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ),
BlockingStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' ),
BlockedQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocked-process/process/inputbuf)[1]','varchar(200)'),
BlockedSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@spid','varchar(20)' ),
BlockedLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@lastbatchstarted','varchar(20)' ),
BlockedSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ),
BlockedStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ),
BlockedStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' )
from fn_trace_gettable (N'd:\trace_blockedprocesses_2013-08-19_0400.trc', default)
cross apply sys.dm_exec_sql_text(cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' )) AS st
where eventclass = 137
order by BlockedLastBatchStarted
anyone else seen this?
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply