LinkedIn | FaceBook | Twitter
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.
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.
1 = Read/write transaction
2 = Read-only transaction
3 = System transaction
4 = Distributed transaction
Defaults to: Full Transaction
The next band shows the data by the Transaction ID from above, and contains the following info:
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.
I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today I'm