Blocked Process Report in SQL 2005 and SQL 2008 | SansSQL

Wednesday, October 28, 2009

Blocked Process Report in SQL 2005 and SQL 2008

Blocked Process Report is a new concept that was introduced in SQL server 2005 onwards. It is an enhancement added to the SQL server profiler.

The Blocked Process Report event class in SQL Server profiler indicates that a task has been blocked for more than a specified amount of time. This event class does not include system tasks or tasks that are waiting on non deadlock-detectable resources.

How to Use:-
To configure the threshold value for Blocked Process Report, use sp_configure.
At first we have to enable the advanced configuration options.
This can be done using the below query

EXEC sp_configure 'show advanced options',1

Then configure the Blocked Process Threshold value. This value will be in seconds. The below query sets the “Blocked Process Threshold” value to 5 seconds.
By default the Threshold value will be 0.

EXEC sp_configure 'blocked process threshold',5

To generate the report, use profiler.

1. Start the Profiler
2. Connect to the server
3. In the Trace Properties, go to Event Selection tab and check the option “Show all events

4. In the events column, expand the “Errors and Warnings” group and select the event “Blocked Process Report

5. Click on run.
6. Now to test the setup, go to SQL Query Editor and run the below query.

USE AdventureWorks
UPDATE HumanResources.Shift SET Name='NewShift' WHERE Name='Night'

7. Now go to a new session or open a new SQL Query Editor and run the below Query.

SELECT * FROM HumanResources.Shift

Since the Begin Tran from the step 6 is still not got the COMMIT or ROLLBACK, this command will be blocked until the COMMIT or ROLLBACK is executed

8. Now go to the profiler and notice that you should have got an event by name “Blocked Process Report” in RED Color.

9. Now click on the Blocked Process Report event to get the detailed Message / Report.

10. In the report it shows 2 sections, one is for the Blocked Process and the other one is for the Blocking Process.
11. Now Execute the below command to release the locks.

This article is also available in pdf format for downloading.
Please Click here to get your copy.


Post a Comment