SQL Server 2005 - Blocked process report

  • Article

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>