SQL Server 2005 - Blocked process report

SQL Server 2005 - Blocked process report

Rate This
  • Comments 1

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>

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
  • Hi Balmukund,

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

    Thanks

    Hemantgiri S. Goswami

Page 1 of 1 (1 items)