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

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

Rate This
  • Comments 2

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.
 

Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post