sys.dm_tran_active_transactions – Day 50 – One DMV a Day

Hello Geeks and welcome to the Day 50 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

I am excited to write this half century blog. I still don’t believe I have been blogging daily since 50 days. Today we are going to learn about another transaction related DMV. Sys.dm_tran_active_transactions, as the name suggests, lists out all active transactions. The information from this DMV is useful. This lets you know the state of each transaction.

Sys.dm_tran_active_transactions has some redundant data from sys.dm_tran_database_transactions. But the other columns include dtc_state related to Azure databases. One other column which is not available in later DMV is transaction_uow. This is the unique identifier for unit of work. It is used by MS DTC to work with distributed transactions.

Let us see the sample output from sys.dm_tran_active_transactions. I will run the same transaction which I am using since last three posts. I will put the code below for isolation of this post.


	 UPDATE pubLogger_tbl
		  SET eName = eName
	 WHERE eId < 100


Now let us run the query on sys.dm_tran_active_transactions. I am selecting only the useful columns. Most of the other columns are only for informational purposes. They will be removed in future releases.

SELECT transaction_id,
	 case transaction_type	 
		  when 1 then 'Read/Write'	 
		  when 2 then 'Read-Only'	 
		  when 3 then 'System'	 
		  when 4 then 'Distributed'	 
		  else 'Unknown - ' + convert(varchar(20), transaction_type)	 
	 end as tranType,	 
	 case transaction_state 
		  when 0 then 'Uninitialized' 
		  when 1 then 'Not Yet Started' 
		  when 2 then 'Active' 
		  when 3 then 'Ended (Read-Only)' 
		  when 4 then 'Committing' 
		  when 5 then 'Prepared' 
		  when 6 then 'Committed' 
		  when 7 then 'Rolling Back' 
		  when 8 then 'Rolled Back' 
		  else 'Unknown - ' + convert(varchar(20), transaction_state) 
	 end as tranState, 
	 case dtc_state 
		  when 0 then NULL 
		  when 1 then 'Active' 
		  when 2 then 'Prepared' 
		  when 3 then 'Committed' 
		  when 4 then 'Aborted' 
		  when 5 then 'Recovered' 
		  else 'Unknown - ' + convert(varchar(20), dtc_state) 
	 end as dtcState, 
FROM sys.dm_tran_active_transactions


You can observe that apart from user transaction, sys.dm_tran_active_transactions also lists out worktable. Worktable are used when using tempDB for storing temporary result sets. They are mostly used in case of sorting and spools. The definition can be read here.

Tomorrow I will be covering one more DMV related to transactions. So stay tuned. Till then.

Happy Learning,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook


About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published.