First published on MSDN on Jan 24, 2012
For some reason I have been looking at DMV output closely the last couple of weeks. I just blogged about the pending I/O requests and now I have a behavior to outline for dm_tran_session_transactions for todays blog.
At this point in time the *active transaction* DMVs on both Server A and B show the enlisted UOW of the DTC transaction. That is, except the sys.dm_tran_session_transactions on server A.
After some digging I uncovered that the dm_tran_session_transactions DMV only outputs rows for sessions ( s ) that are NOT system level sessions. Since the broker activity is handled on a system session the DMV will not materialize a row for Session 20 s on Server A in this example. Instead you have to use the additional *active transaction* tables to track the UOW across this system.
Note: I am able to use any transaction (local or DTC) as part of SQL Service Broker activation which will not show rows in the session transactions DMV because it is considered a system session.
For some reason I have been looking at DMV output closely the last couple of weeks. I just blogged about the pending I/O requests and now I have a behavior to outline for dm_tran_session_transactions for todays blog.
select * from sys.dm_tran_session_transactions
The scenario I was looking at is as follows.
Server A Server B Broker calls activation procedure Begin Tran (local) Linked Server Query Transaction Promoted to DTC Transaction Imported from Server A xp_cmdshell Looped back and blocked
Separate TransactionBlocking
- The process is based on SQL Service Broker.
- The service broker session on Server A is 20 s .
- It starts a local transaction and eventually performs a linked server query.
- This causes the transaction to be promoted to a DTC transaction.
At this point in time the *active transaction* DMVs on both Server A and B show the enlisted UOW of the DTC transaction. That is, except the sys.dm_tran_session_transactions on server A.
After some digging I uncovered that the dm_tran_session_transactions DMV only outputs rows for sessions ( s ) that are NOT system level sessions. Since the broker activity is handled on a system session the DMV will not materialize a row for Session 20 s on Server A in this example. Instead you have to use the additional *active transaction* tables to track the UOW across this system.
Note: I am able to use any transaction (local or DTC) as part of SQL Service Broker activation which will not show rows in the session transactions DMV because it is considered a system session.
Bob Dorr - Principal SQL Server Escalation Engineer
Updated Jan 15, 2019
Version 2.0mssql-support
Former Employee
Joined January 15, 2019
SQL Server Support Blog
Follow this blog board to get notified when there's new activity