Using the Blocked Process Report in SQL Server 2005/2008

  • rafa.aborges

    Default port

    Points: 1430

    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?

  • don.schaeffer

    Ten Centuries

    Points: 1165

    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.

  • rafa.aborges

    Default port

    Points: 1430

    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?

  • rafa.aborges

    Default port

    Points: 1430

    Any ideas?

  • rafa.aborges

    Default port

    Points: 1430

    Anyone?

  • doc_sewell

    SSC Eights!

    Points: 820

    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?

  • doc_sewell

    SSC Eights!

    Points: 820

    Jonathan,

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

  • manoj.ks

    Old Hand

    Points: 348

    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?

  • pkrudysz

    Right there with Babe

    Points: 742

    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.

  • pkrudysz

    Right there with Babe

    Points: 742

    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?

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    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]

  • pkrudysz

    Right there with Babe

    Points: 742

    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.

  • NJ-DBA

    SSChampion

    Points: 13832

    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 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply