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.

BEGIN TRANSACTION

	 UPDATE pubLogger_tbl
		  SET eName = eName
	 WHERE eId < 100

--COMMIT

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,
	 name,
	 transaction_begin_time,
	 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, 
	 transaction_uow
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,
Manu

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