When I execute the two queries below,
SELECT session_id, transaction_id FROM sys.dm_tran_session_transactions;
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 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?
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.