Using the Blocked Process Report in SQL Server 2005/2008

  • 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?

  • 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.

  • 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?

  • Any ideas?

  • Anyone?

  • 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?

  • Jonathan,

    What about turning the blocked process threshold off when you are done?

  • 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?

  • 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.

  • 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?

  • 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]

  • 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.

  • 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