Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
Use the blocked process threshold option to specify the minimum threshold, in seconds, at which blocked process reports are generated. The actual time to detect a blocked process might be several seconds longer than the configured value. The option accepts values from 0 to 86,400; 0 disables reporting. Low threshold values can substantially increase the number of reports when many tasks are blocked.
By default, no blocked process reports are produced. This event isn't generated for system tasks or for tasks that are waiting on resources that don't generate detectable deadlocks.
You can define an alert to be executed when this event is generated. So for example, you can choose to page the administrator to take appropriate action to handle the blocking situation.
Blocked process threshold uses the deadlock monitor background thread to walk through the list of tasks waiting for a time greater than or multiples of the configured threshold. The event is generated once per reporting interval for each of the blocked tasks.
The blocked process report is done on a best effort basis. There's no guarantee of any real-time or even close to real-time reporting.
The setting takes effect immediately without a server stop and restart.
Examples
The following example sets the blocked process threshold to 20 seconds, generating a blocked process report for each task that is blocked.
EXECUTE sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXECUTE sp_configure 'blocked process threshold', 20;
GO
RECONFIGURE;
GO