Post reply

Find the object name which is causing blocking

  • December 6, 2011 at 6:08 pm

    #391054

    Hi,

    How to find root cause of blocking. If SPID 1 is blocking SPID 2,3,4,5,6 then how can I find the object name in SPID 1.

    Thanks in advance.

  • December 7, 2011 at 12:11 am

    #1417802

    the blocked process report , would be a good option here.

    http://www.sqlpassion.at/blog/PermaLink,guid,0cf203a4-963f-4db0-b26f-4e4d3fb41610.aspx


    Clear Sky SQL
    My Blog[/url]

  • December 7, 2011 at 12:32 am

    #1417814

    beejug1983 (12/6/2011)

    Hi,

    How to find root cause of blocking. If SPID 1 is blocking SPID 2,3,4,5,6 then how can I find the object name in SPID 1.

    Thanks in advance.

    Execute below command in query window:

    DBCC INPUTBUFFER (SPIDNumberHere) to get the command that is being executed with that SPID.

    Sujeet Singh

  • December 7, 2011 at 2:09 am

    #1417855

    i always thought that DBCC INPUTBUFFER only returns the last statement in the batch which is being executed, so you might give it 10 commands to do and it will show command 10, not say command 3 which is currently blocking, but i could be wrong.

    i would say to use the DMV's

    something like the below

    select text from sys.dm_exec_requests

    cross apply sys.dm_exec_sql_text (sql_handle)

    where session_id = ?

    Where ? is the head blocker

Viewing 4 posts - 1 through 3 (of 3 total)

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