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:
Second Output:
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
