SQL Server Management Studio Standard Reports – All Transactions: [DatabaseName] - Carpe Datum - Site Home

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 is very similar to the Transaction reports I’ve covered in the other posts in this series. Because this is a database-level report, it only focuses on one database at a time, and it gives you more lock information than the server-level reports.

The first band shows the some general statistics about the “Wait” status of the system. Waits are just that; the time taken by the system waiting on something else. Normally these require investigation:

Column	Description
Average wait time (ms)	The average time taken in the database waiting on another resource.
Waits in progress	Number of processes currently waiting.
Waits started per second	Number of waits started per second.
Cumulative wait time (ms) per second	Aggregated wait time per second.

The second band grouping has breakdowns from the State of the transactions, then on to the sessions, then the individual transactions, the resource type and down to the lock type:

Column	Description
State	Shows whether the transaction is active or not.
# Transactions	The number of transactions in the state.
Session ID	The session ID associated with a transaction.
Host Name	The name of the host machine that initiated the transaction.
Program Name	The name of the program that initiated the transaction.
Login Name	The name of the user account that initiated the transaction.
Login Time	The time that the user initiating the transaction logged in.
Host Process ID	The operating system number assigned to the transaction.
Transaction ID	The ID of the transaction, assigned by SQL Server.
Transaction Name	The name of the transaction. If not named, this defaults to “user_transaction”.
# Total Locks	The total number of locks this transaction is taking.
Transaction Type	Whether this transaction is a Full transaction or part of another.
Transaction Start Time	The time this transaction started.
Isolation Level	The transaction isolation level, which deals primarily with how the locks will be taken. More about those here.
Resource Name	The table or other object involved in the transaction.
Lock Type	The type of lock taken on the object. More here.
Request Mode	The type of lock requested. More here.
# Locks Granted	The number of locks the system has granted to this Resource.
# Locking Requests Waiting	The number of lock requests waiting for this Resource.