This option allows an administrator to
request a notification when a user task has been blocked for more than the
configured number of seconds. When Blocked Process Threshold is set to 0, no notification
is given.
You can set any value up to 86,400 seconds. When the deadlock monitor
detects a task that has been waiting longer than the configured value, an
internal event is generated. You can choose to be notified of this event in one
of two ways. You can use SQL Trace to create a trace and capture event of type
Blocked process report, which you can find in the Errors and Warnings category
on the Events Select screen in SQL Server Profiler. So long as a resource stays
blocked on a deadlock-detectable resource, the event is raised every time the
deadlock monitor checks for a deadlock. An Extensible Markup Language (XML)
string is captured in the Text Data column of the trace that describes the
blocked resource and the resource being waited on.
Alternatively, you can use event notifications
to send information about events to a service broker service. Event notifications
can provide a programming alternative to defining a trace, and they can be used
to respond to many of the same events that SQL Trace can capture. Event notifications,
which execute asynchronously, can be used to perform an action inside an
instance of SQL Server 2008 in response to events with very little consumption
of memory resources. Because event notifications execute asynchronously, these
actions do not consume any resources defined by the immediate transaction.
