Finding active transactions

Have you ever forgot to commit a transaction? Maybe you’ve even left for lunch and caused a problem while you were gone. Uncommitted transactions can cause all sorts of problems–not just blocking, but it can affect your Availability Groups & Mirroring, your TempDB version store if you’re using one of the snapshot isolation levels.

Finding open transactions

Paul Randal (blog|twitter) has an example script to find open transactions. Paul’s done all the hard work for us. He uses sys.dm_tran_session_transactions and sys.dm_tran_database_transactions to identify open transactions, then combines with some other DMVs to get key details about the sessions.

If you read my recent post on Finding the leading blocker, you won’t be surprised to hear I want my script to do all the things.

I want more

I’m fussy. Paul’s example script is awesome, but I want to tweak it to meet my needs. I also want to wrap it into a stored procedure so that I can put it in my DBA database, and have it installed on every server I manage. In addition to the columns Paul pulls back, I want to find out:

• More session details: host name, application name, etc
• More transaction info: Transaction state, duration, etc
• Filter out very short transactions: I want to filter out the noise for regular activity

There’s also a transaction state that I exclude. Transaction State 3 means “The transaction has been initialized but has not generated any log records.” A transaction in this state won’t affect log reuse. However, it could affect the size of the version store in TempDb if you’re using Read Committed Snapshot Isolation. I haven’t run into this as a problem, so I filter it out.

I used dbo.Check_Blocking as a base to create another check script to check open transactions

dbo.Check_OpenTransactions

Because I’ve added this to my DBA database, if I want to look at who or what is holding open transactions, I just run EXEC DBA.dbo.Check_OpenTransactions. Here’s what the output looks like:

Notice that that this session has open transactions affecting two different databases: DBA and AdventureWorks2014. Also notice that the TransactionStart time is different for each database. This reflects the fact that the same session–the same transaction–made changes to two different databases, about 20 minutes apart, and is still open more than 3 hours later. The output gives me the HostName (my laptop), LoginName (me), and Program Name (SSMS) that is involved in this transaction. Those three data points are usually enough to track down the person or process that is behaving badly. In particular, I know that a person on a workstation using SSMS is nearly always a forgotten COMMIT/ROLLBACK. A server or an application process can get a little more complicated, but at least you have the info you need to chase it down.

The output also will show you if the open transactions are being blocked by another session (BlockingSpid), and exactly how long the transaction has been open (TransactionLegthMinutes). You can even see TransactionCount & TransactionState (more on this later).

If you scroll to the right of the result set, you’ll see some familiar columns–these are the same columns I use in dbo.Check_Blocking, which shows the wait time & resource (if it’s waiting), and the various flavors of “what is running”, and an XML column with additional Session Info:

Lets take a look at that XML:

This sums up all the information in the other columns in one easy-to-reach chunk (if you can say XML is ever “easy to read”). This allows me to quickly grab all the details for an open transaction so that I can follow-up later with the appropriate person to prevent future problems.  Also notice that I’ve decoded the TransactionState into plain English. You don’t need a magic decoder ring to translate the integer TransactionState to plain English–you just need to read the documentation. But here’s the summary of Transaction State meanings:

• 1 = The transaction has not been initialized.
• 3 = The transaction has been initialized but has not generated any log records.
  • This means there has been a BEGIN TRAN, but nothing has been changed yet, and nothing has been written to the transaction log.
• 4 = The transaction has generated log records.
  • This means there has been a BEGIN TRAN, and things have been changed yet, and things have been written to the transaction log. Transactions in this state will prevent log reuse, and could cause blocking.
• 5 = The transaction has been prepared.
• 10 = The transaction has been committed.
• 11 = The transaction has been rolled back.
• 12 = The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.

States 1, 5, 10, 11, 12 are not common. I believe they are all transient states as SQL Server creates & completes transactions–but the majority of open transactions you see will be in states 3 or 4. My code filters out 3, so pretty much everything returned by this procedure will be in state 4.

This XML chunk is part of my plan of how I can save results as part of root-cause analysis. I can copy a cell, or the entire column, paste it into OneNote, and have everything I need to get to the bottom of things. In my example above, that might mean slapping someone on the wrist for leaving a transaction uncommitted. In another scenario, it might mean tuning code to be faster, or changing the code around so transactions are more atomic and quicker. Regardless, you can’t find the cause unless you have the info.

Give it a try

Download dbo.Check_OpenTransaction and try it on your environment. Or feel free to grab the whole DBA database project and install the whole thing.