sys.dm_tran_session_transactions – Day 49 – One DMV a Day

Hello Geeks and welcome to the Day 49 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.

Yesterday we have talked about sys.dm_tran_database_transactions. Also I have mentioned that sys.dm_tran_session_transactions can be joined to it. This will help to get session details for a transaction. Today I am going to cover sys.dm_tran_session_transactions.

Sys.dm_tran_session_transactions lists out the open transactions for a single session. The output contains columns like enlist_count. This gives the number of requests active in a session. This is possible in case of MARS. So you will see multiple transaction ids for a single session when this number is more than 0.

Is_local tells if this transaction is local or enlisted distributed. Is_enlisted specifies if this is an enlisted distributed transaction. Is_bound specifies if this session is active on the session by bound session. By distributed transactions and bound sessions it is possible for a transaction to run on multiple sessions. In this case you would notice multiple rows for each session with same transaction id.

Other columns let us know if the transaction is user or system (is_user_transaction). Number of open transactions from the current session is also available. To see the usage of sys.dm_tran_session_transactions, I will run below query to see open_transaction_count from a session.

BEGIN TRANSACTION

	 UPDATE pubLogger_tbl
		  SET eName = eName
	 WHERE eId < 100
-- Check output from sys.dm_tran_session_transactions for this session_id
	 BEGIN TRAN tran2

		  UPDATE pubTrans_tbl
			   SET eName = eName
-- Check output from sys.dm_tran_session_transactions for this session_id
	 /*COMMIT
COMMIT*/

As specified in the query run the below query at the specific intervels. Sys.dm_tran_session_transactions will return below output.

SELECT session_id,
	 transaction_id,
	 transaction_descriptor,
	 enlist_count,
	 is_user_transaction,
	 is_local,
	 is_enlisted,
	 is_bound,
	 open_transaction_count
FROM sys.dm_tran_session_transactions
WHERE session_id = 60 --Change session id as needed

First Output:
sys.dm_tran_session_transactions

Second Output:
sys.dm_tran_session_transactions

In the above output you can observe that first result has only 1 open transaction for session id 60. When I opened another transaction it has increased the count to 2. One more interesting column in sys.dm_tran_session_transactions is transaction_descriptor. This is used internally by SQL Server to communicate with the client driver.

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

Avatar

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. Required fields are marked *