SQL Server Management Studio Standard Reports – All Blocking Transactions: [DatabaseName]

I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today we’re in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears, click the “Standard Reports”, and then select the title at the top of this post.

This report shows information similar to the Locking and Blocking reports I’ve covered in the other posts in this series, but since this is a database-level report, it only focuses on one database at a time, and it gives you more lock information than some of the server-level reports.

The first band groups the blocking information by the Transaction ID, which is a unique number assigned to each unit of work the server is doing. You can use this number to locate lots of other information in many of the Dynamic Management Views or other Meta-Data views:

Column	Description
Transaction ID	A unique number assigned to every transaction – you can use this to join on other meta-data
# Directly Blocked Transactions	The number of transactions this transaction is blocking as a direct result of its actions, rather than by being part of a chain that eventually blocks another transaction
# Indirectly Blocked Transactions	The number of transactions this transaction is blocking by being part of a chain that eventually blocks another transaction, rather than from a direct result of its actions
Transaction Name	The name of the transaction assigned by the developer, otherwise the first part of the first statement
State	Whether the transaction is actively doing anything or not
Transaction Type	The type of transaction – Read only, read/write, or a system transaction
Start Time	The time this transaction started
Resource Type	The resource that is blocking, such as a database, table or page
Session ID	The unique ID of the session, which you can link to other meta-data views
Blocking SQL Statement	Partial text of the statement that is causing the blocking

The second band starts a grouping for the Blocking SQL Statement.

The third band groups the information into Direct or Indirect blocks. A direct block results from a statement that blocks another, and an indirect block happens when one statement causes a cascading effect that eventually causes a lock. This is useful information you can use to track down the actual cause of the blocking behavior.

Column	Description
Direct/Indirect	Direct if the statement is directly blocking the resource, Indirect if it is part of a blocking chain
Blocked Transaction ID	The ID of the transaction that is being blocked
Blocked Transaction Name	If the transaction is named, you’ll see that here, otherwise it is the first part of the SQL statement that is being blocked
State	Whether the statement is active or not
Transaction Type	The type of transaction – Read only, read/write, or a system transaction
Start Time	The time this transaction was blocked
Resource Type	The
Session ID	The resource that is being blocked , such as a database, table or page
Blocked SQL Statement	Partial text of the statement that is being blocked

The last band groups its information by the Blocked SQL Statement. You’ll see the previous columns duplicated, which is useful in a blocking chain.