SQL Server Management Studio Standard Reports – Activity - All Blocking Transactions - Carpe Datum - Site Home

I’m continuing a series on the Standard Reports you can find in SQL Server Management Studio (SSMS).

We're now up to the "Activity" reports, which show more information than you'll find in SQL Server Management Studio in the "Activity Monitor" node. That view is more immediate; these reports show more detail. This particular report shows a lot of information about transaction blocking. If you're not familiar with transactions, look here for more information. If you're not familiar with locking in SQL Server, look here for more information.

This report will only show something if you have blocking transactions. You'll have multiple descendig grids within a "parent" one if you have any blocks. Here are the columns you'll see in each:

TransactionID	The ID of the Transaction that is blocking other transactions
# Directly Blocked Transactions	the number of statements this session is blocking
# Indirectly Blocked Transactions	The number of downstream transactions this one causes to block others
Transaction Name	Name of the transaction, if set. Otherwise, this is set to "user_transaction"
State	Whether this transaction is active or not
Transaction Type	Whether this transaction is a complete statement or a fragment of another
Start Time	The time this transaction started blocking another
Resource Type	The type of lock taken
Session ID	The session ID of the transaction - this is the number that uniquely identifies the statement that also shows up when you do an sp_who2 statement.
Blocking SQL Statement	the portion of the transaction where the blocking occurs. This doesn't always indicate the exact offending statement.

The format of this gried layout is that it shows the blocking session at the top, with all of the statements it blocks stepped down below it. The columns are the same for each.

To find this report, open SSMS, right-click a Server, and then select “Reports” and then “Standard Reports”.This report takes a few moments to render.