Database Reference
In-Depth Information
The event fields are all preselected for you. If you still have the queries running from the previous section that
created the block, all you need to do now is click the Run button to capture the event. Otherwise, go back to
Listings 20-1 and 20-2 and run them in two different connections. After the blocked process threshold is passed, you'll
see the event fire . . . and fire. It will fire every five seconds if that's how you've configured it and you're leaving the
connections running from Listings 20-1 and 20-2. The output in the live data stream looks like Figure
20-16
.
Figure 20-16.
Output from the blocked_process_report event
Some of the information is self-explanatory; to get into the details, you need to look at the XML generated in the
blocked_process
field.
<blocked-process-report monitorLoop="29133">
<blocked-process>
<process id="process26911b848" taskpriority="0" logused="0" waitresource="RID: 5:1:24292:0"
waittime="5331" ownerId="255377" transactionname="User2" lasttranstarted="2014-03-26T20:31:40.427"
XDES="0x26b386d70" lockMode="S" schedulerid="1" kpid="2340" status="suspended" spid="55"
sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-03-26T20:31:40.427"
lastbatchcompleted="2014-03-26T20:31:40.423" lastattention="1900-01-01T00:00:00.423"
clientapp="Microsoft SQL Server Management Studio - Query" hostname="WIN-TUTJRM1T45J" hostpid="2324"
loginname="WIN-TUTJRM1T45J\Administrator" isolationlevel="read committed (2)" xactid="255377"
currentdb="5" lockTimeout="4294967295" clientoption1="671098976" clientoption2="390200">
<executionStack>
<frame line="2" stmtstart="24" stmtend="118" sqlhandle="0x02000000ccf3e6045e680885750c3f36d7cc54
9d8ff013680000000000000000000000000000000000000000" />
<frame line="2" stmtstart="36" stmtend="134" sqlhandle="0x0200000063e12d309fa7874804b7b56c7be7be
ecf2a0255b0000000000000000000000000000000000000000" />
</executionStack>
<inputbuf>
BEGIN TRAN User2
SELECT C2
FROM dbo.BlockTest
WHERE C1 = 11;
COMMIT </inputbuf>
</process>
</blocked-process>
