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.

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

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