SQL Server Standard Reports – Top Transactions by Blocked Transactions Count: [DatabaseName]

In this continuing series on the Standard Reports in SQL Server Management Studio, we’re now up to the database-level reports, and we’re in the transaction-level reports at the moment. To get to this report, navigate from the server object in Object Explorer, move down to the Databases object, and then right-click any database. From the menu that appears, select Reports, then Standard Reports, and then this one.

At the server-level, I explained many of the reports dealing with transactions, and this report has much of the same information, but it is scoped to a single database. I often use the server reports to locate any issues, and if I find some I use this report (and the ones that follow) to narrow the search.

Here are the major parts of the report. The first band shows the Top Blocking Transactions, and the following bands show the blocking SQL Server Statement, whether the block is direct or indirect, and then the blocked SQL Statement. Since some of the columns repeat in these bands, I’ll just explain them all in a single table :

Column	Description
Tran. ID	A unique number that identifies the transaction – you can use this as a key to join to other meta-data tables and views.
# Directly Blocked Transactions	The number of transactions directly blocked by this statement.
# Directly Blocked Transactions	The number of transactions indirectly blocked by this statement, for instance this statement blocks another which is required by yet another transaction.
Tran. Name	If the transaction has been named by the developer, that name shows up here. Otherwise, you will see “user_transaction”
State	Whether the transaction is Actively working or not.
Tran. Type	Whether this is a full transaction or part of another.
Start Time	The time the transaction started
Resource Type	Represents the resource type. The value can be one of the following: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, or ALLOCATION_UNIT.
Session ID	A unique number assigned to this session, which you can use to join with other meta-data tables or views.
Blocking SQL Statement	A portion of the text the server is running in this transaction that is blocking another.