Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I execute the two queries below,

SELECT 
    session_id, transaction_id
    FROM sys.dm_tran_session_transactions;

AND

SELECT 
    session_id, request_id, at.transaction_id
    FROM sys.dm_tran_active_transactions at
        JOIN sys.dm_exec_requests r
            ON r.transaction_id = at.transaction_id;

I've read the BOL for both 1 and 2 but don't see any clear explanation as to why the difference would occur.

I get different results. The former query returns no results, but the latter returns active transactions with session and transaction ids. The request_id is 0 which, I think, means that it's the only request made by the session. Could someone help me understand why there is a difference between the two concepts I've queried above?

EDIT

I just reran the queries and now I get a result for the first DMV which has a session_id that is not actually contained in the second result set.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

It appears that sys.dm_tran_session_transactions does not include auto-commit transactions:

-- No result
SELECT 
    session_id, 
    transaction_id
FROM sys.dm_tran_session_transactions;

Using an explicit transaction does return results:

BEGIN TRANSACTION;

    -- Row returned    
    SELECT 
        session_id, 
        transaction_id
    FROM sys.dm_tran_session_transactions;

ROLLBACK TRANSACTION;

The DMVs are views on internal structures, and the documentation is not often as comprehensive as in other areas. Some of that may be because it would be inconvenient to go through a complete deprecation cycle each time a change in DMV behaviour occurs, but it's likely just an oversight in this case. You could report the documentation deficiency on Connect.

Adam Machanic found all sorts of odd little behaviours with the DMVs while writing his sp_WhoIsActive tool. If it suits your purposes, you could use that rather than trying to write your own monitoring queries.

share|improve this answer
    
Thanks Paul. I definitely use WhoIsActive but have just been trying to acquaint myself with DMVs and what they do and what purpose they serve. I appreciate your answer –  swasheck Oct 2 '13 at 14:44

sys.dm_tran_session_transactions is an intermediate view allowing to join the DMV's that are primarily identified on session_id column - sys.dm_exec* DMV's with other sys.dm_tran_* DMV's. This view will tell if the transaction is a user transaction is_user_transaction = 1 or a system transaction with is_user_transaction = 0.

On the other hand, sys.dm_tran_active_transactions - is the DMV that will store transactional information showing status, state of each transaction - initiated but not completed, type, etc on the sql server instance. It also gives info on distributed transactions as well. This DMV will give results for all databases on the server instance and it is a point-in-time snapshot of currently active transactions - results will change each time the query is executed as the sate of the individual transaction will change.

Refer to sys.dm_tran_session_transactions and sys.dm_tran_active_transactions for column lists and what each column means.

share|improve this answer
    
That was my initial thought as well. What I'm asking is why the session_transaction DMV doesn't have the same information since a session has initiated a request that has an open and active transaction. Essentially, why aren't my active transactions in the session transaction intermediate view? –  swasheck Oct 1 '13 at 20:05
    
@swasheck Just curious, are you using service broker (local or DTC) ? This will be helpful How It Works: sys.dm_tran_session_transactions (blogs.msdn.com/b/psssql/archive/2012/01/24/…). sys.dm_tran_session_transactions DMV only outputs rows for sessions (s) that are NOT system level sessions. –  Kin Oct 2 '13 at 1:24
    
Nope. Not using service broker –  swasheck Oct 2 '13 at 13:36

Both DMVs are a snapshot of what is happening at the precise moment they are executed. It is therefore highly likely on a busy system that running

SELECT 
session_id, transaction_id
FROM sys.dm_tran_session_transactions;

SELECT 
session_id, transaction_id
FROM sys.dm_tran_session_transactions;

could display differing result sets.

share|improve this answer
    
Likely, but not the case here. Thanks. I've discovered that the transactions are distributed across databases. I wonder if that should tell me anything. –  swasheck Oct 1 '13 at 22:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.