Post reply

blocking processes on a SQL Server instance

  • January 24, 2012 at 6:17 am

    #255348

    Hello,

    Actually I capture blocking processes via Alerts on my SQL SERVER INSTANCE (WINDOWS SERVER 2003 - SQL 2005 SP3 ENTREPRISE 64Bits). I have a problem to understand "who block who".

    Could you explain how I can read and understand the following message ?

    <TextData>

    <blocked-process-report monitorLoop="75777">

    <blocked-process>

    <process id="process3a31198" taskpriority="0" logused="232" waitresource="OBJECT: 15:1735403139:4 " waittime="6125" ownerId="1630648095" transactionname="user_transaction" lasttranstarted="2012-01-23T15:10:44.353" XDES="0xab2c536a0" lockMode="IX" schedulerid="5" kpid="2036" status="suspended" spid="318" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2012-01-23T15:10:44.357" lastbatchcompleted="2012-01-23T15:10:44.353" clientapp=".Net SqlClient Data Provider" hostname="DI66SW" hostpid="2116" loginname="CNETEXP" isolationlevel="read committed (2)" xactid="1630648095" currentdb="15" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

    <executionStack>

    <frame line="35" stmtstart="1672" stmtend="2800" sqlhandle="0x03000f00dd177e0f050c4301589900000100000000000000" />

    </executionStack>

    <inputbuf>

    Proc [Database Id = 15 Object Id = 259921885] </inputbuf>

    </process>

    </blocked-process>

    <blocking-process>

    <process status="suspended" waitresource="15:1:7678413" waittime="15" spid="611" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2012-01-23T15:10:35.620" lastbatchcompleted="2012-01-23T15:10:35.620" clientapp=".Net SqlClient Data Provider" hostname="DI68SW" hostpid="1912" loginname="CNETEXP" isolationlevel="read committed (2)" xactid="1630523255" currentdb="15" lockTimeout="4294967295" clientoption1="536870944" clientoption2="128056">

    <executionStack>

    <frame line="309" stmtstart="21692" stmtend="24690" sqlhandle="0x03000f002d24f341ffba5501a59d00000100000000000000" />

    </executionStack>

    <inputbuf>

    Proc [Database Id = 15 Object Id = 1106453549] </inputbuf>

    </process>

    </blocking-process>

    </blocked-process-report>

    </TextData>

    Thanks,

    Eric

  • January 24, 2012 at 6:33 am

    #1437637

    spid 318 is blocked by spid 611

    Check this article

    http://www.sqlservercentral.com/articles/Blocking/64474/

  • January 24, 2012 at 7:24 am

    #1437671

    Chapter 6 - http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 24, 2012 at 7:30 am

    #1437676

    <blocking-process> is the culprit and <blocked-process> is the victim.

  • January 24, 2012 at 9:16 am

    #1437757

    I use a script to get the blocking query info. The attached script will give you the SPID, query text, loginname and hostname of the blocked process as well as the blocking process. There is small bug in the script which I am trying to fix but for the most part it works fine and will indeed give you the correct SPID that is causing the blocking.

    http://saveadba.blogspot.com/

    Blog
    http://saveadba.blogspot.com/

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

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