SQL Server 2005 – Blocked process report


In SQL Server 2000 and earlier version, there was no easy way to find blocking on server. You need to capture Blocker script and do analysis. Going forward, in SQL Server 2005 there is new event in profier trace called “Blocked Process Report” Event Class. I have seen questions like why this is not getting generated.


As per Books online


use the sp_configure command to configure the blocked process threshold option, which can be set in seconds. By default, no blocked process reports are produced.


 Below is sample blocking.


=====Conmection1=====


use tempdb


go


create table blocked_test ( i int)


go


insert into blocked_test values (1)


go


begin tran


update blocked_test


 set i = 2


=====Conmection2===== 


select * from blocked_test


Below is the same output of the report.


 


<blocked-process-report monitorLoop=”2034″>
 <blocked-process>
  <process id=”processb0aa78″ taskpriority=”0″ logused=”0″ waitresource=”RID: 2:1:158:0″ waittime=”8533″ ownerId=”17714″ transactionname=”SELECT” lasttranstarted=”2007-10-16T13:26:15.853″ XDES=”0x5450ac8″ lockMode=”S” schedulerid=”1″ kpid=”4564″ status=”suspended” spid=”53″ sbid=”0″ ecid=”0″ priority=”0″ transcount=”0″ lastbatchstarted=”2007-10-16T13:26:15.837″ lastbatchcompleted=”2007-10-16T13:23:48.750″ clientapp=”Microsoft SQL Server Management Studio – Query” hostname=”TestMachine” hostpid=”7948″ loginname=”blakhani” isolationlevel=”read committed (2)” xactid=”17714″ currentdb=”2″ lockTimeout=”4294967295″ clientoption1=”671090784″ clientoption2=”390200″>
   <executionStack>
    <frame line=”1″ sqlhandle=”0x0200000069e73f03272b0d3e06153e49ef20afd20bff2f32″/>
   </executionStack>
   <inputbuf>
select * from blocked_test   </inputbuf>

  </process>
 </blocked-process>
 <blocking-process>
  <process status=”sleeping” spid=”52″ sbid=”0″ ecid=”0″ priority=”0″ transcount=”1″ lastbatchstarted=”2007-10-16T13:23:47.320″ lastbatchcompleted=”2007-10-16T13:23:47.353″ clientapp=”Microsoft SQL Server Management Studio – Query” hostname=”TestMachine” hostpid=”7948″ loginname=”blakhani” isolationlevel=”read committed (2)” xactid=”17195″ currentdb=”2″ lockTimeout=”4294967295″ clientoption1=”671090784″ clientoption2=”390200″>
   <executionStack/>
   <inputbuf>


begin tran
update blocked_test
set i = 2   </inputbuf>

  </process>
 </blocking-process>
</blocked-process-report>


Comments (1)

  1. Hemantgiri S. Goswami says:

    Hi Balmukund,

    The link for Blocker Script is broken, is it support.microsoft.com/…/271509 ?

    Thanks

    Hemantgiri S. Goswami