Blocked Process Threshold Option – A useful New Feature of SQL Server 2005

SQL Server 2005 introduces a new advanced system configuration (sp_configure) option called "blocked process threshold." We can use this option to proactively monitor blocking and deadlocking instances. You can use sp_configure to set the blocked process threshold to an integer value between 0 (the default) and 86400 (24 hours). If you have set the blocked process threshold configuration value to 15. Now if there is a blocking for over 15 seconds, SQL Server will raise an event that can be captured by using the SQL Server event notification mechanism to perform an action, and the event can also be seen in SQL Profiler. The event is raised every 15 seconds after that until the blocking is resolved.

SQL Server raises the BLOCKED_PROCESS_REPORT event when there is a blocking for 15 seconds and every 15 seconds after that until blocking is resolved. This event can be captured by using the event notification mechanism introduced in SQL Server 2005. The event notification mechanism captures the event and notifies a Service Broker service by placing a message in the queue


Reference : http://msdn2.microsoft.com/en-us/library/ms181150.aspx