SQL Server Management Studio Standard Reports - Activity – Top Transactions by Age

I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today I'm covering another "Activity" report. This report shows more information about transactions, which I’ve covered in some of the other reports. This one, however, arranges them by age. This helps identify long-running queries, queries that are “stuck” (being blocked) or just plain old.

If you’re new to SQL Server, you might not be familiar with the idea of a transaction. A transaction is simply a block of work to do that is kept together as a unit. I always use the analogy of an automatic bank teller (ATM) machine. Let’s say you transfer 100.00 from checking to savings. You want the money to come out of one account AND go into the other, or you don’t want anything at all to happen. That’s a transaction – just a unit of work that you want to happen or not. SQL Server (like all RDBMS systems) uses transactions. In SQL Server, every connection, command, and disconnection is treated as a transaction implicitly – that is, you don’t have to do anything for them to be a transaction. You can also create your own transactions (using the BEGIN TRANSACTION statement) and even give them names. While many developers don’t name their transactions, it’s a great habit to get into – it will make troubleshooting code a lot easier, as you’ll see in a moment.

Tracking transactions is critical to two processes: performance tuning and problem solving. You can use this report to help you track down transactions that have been running a long time. The first section organizes the information by the top oldest transactions.

First Band:

Tran. ID	ID of the transaction at the instance level, not the database level. It is only unique across all databases within an instance but not unique across all server instances.
Tran. Name	Transaction name. This is overwritten if the transaction is marked and the marked name replaces the transaction name. If not specified, it is user_transaction
State	A number representing the state of the transaction: 0 = The transaction has not been completely initialized yet. 1 = The transaction has been initialized but has not started. 2 = The transaction is active. 3 = The transaction has ended. This is used for read-only transactions. 4 = The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place. 5 = The transaction is in a prepared state and waiting resolution. 6 = The transaction has been committed. 7 = The transaction is being rolled back. 8 = The transaction has been rolled back.
Tran. Type	Shows the type of transaction: 1 = Read/write transaction 2 = Read-only transaction 3 = System transaction 4 = Distributed transaction Defaults to: Full Transaction
Start Time	Shows the time that the transaction started
Isolation Level	You can control a lot of locking behavior using a Transaction Isolation Level in your code. You can find out more about those here.
Session ID	A number that uniquely identifies the session for the transaction.
Login Name	The login name of the user running the transaction – note that this can be set in code and can be affected if you’re running in an application role.

The next band shows the data by the Transaction ID from above, and contains the following info:

Database Name	Name of the database the transaction is running in.
Tran. State	The state of the transaction = this is the same value as above.
# Locks	The number of locks this transaction is taking.
First Update Time	Shows the first time the transaction was updated.

And the final band, per Transaction ID again, shows the Currently Executing SQL Statement, although not all of it. Many times this is useful, but if it is a long string that reoccurs often, it isn’t as useful.