DaveWentzel.com            All Things Data

Blocked Process Report

 

In 2005 there is the Blocked Process Report which can be captured in a trace and can trigger code to handle notification or remediation.  But it only detects blocking on resources that support deadlock detection, so again, not all blocking can be caught, but probably everything that you can control will be.  Wait types that handle this:
  • LCK_M_*
  • CXPACKET, EXCHANGE, other parallel query issues
  • RESOURCE_SEMAPHORE (memory)
  • CLR stuff
You need to set a threshold using something like:
 
sp_configure 'show advanced options',1

go

reconfigure

go

sp_configure 'blocked process threshold',<some number of seconds>

go

reconfigure

go

Add new comment