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.