How to list blocking transactions

Owned by Scott Pearce
Last updated: Jun 30, 2020
2 min read

Blocking is simply a term that means that one connection is holding a lock on a resource when another connection wants to read or write to it. It doesn't necessarily mean that the owner connection won't release it, just that it's currently holding it. Deadlocks can happen when you have two connections that each have a lock, but they want each others resource. Each connection needs access to both of the resources in order to continue. However, they are in a state of permanent blocking. In this state, the database engine has to come in and choose a loser so that one of the connections can have access to both of the resources. It is possible for a deadlock situation to not be detected by the database engine, in which case human intervention is required (i.e. manually kill one of the connections, or reboot the entire server).

Step-by-step guide

To obtain a list of blocking transactions:

  1. Open Microsoft SQL Server Management Studio and connect to the SQL Server

  2. Right-click the server object

  3. Expand Reports, expand Standard Reports

  4. Click "Activity – All Blocking Transactions":

  5. Right-click on the report to export it to PDF for later analysis:

 

 

This report shows the transactions at the head of blocking chain. If you expand the transaction, the report will show the transactions that are blocked by the head transaction. This report will also show the "Blocking SQL Statement" and the "Blocked SQL Statement."

Related articles