How It Works: sys.dm_tran_session_transactions

Published Jan 15 2019 02:12 PM 569 Views
Microsoft
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.

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 Transaction
Blocking


  • 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

%3CLINGO-SUB%20id%3D%22lingo-sub-316802%22%20slang%3D%22en-US%22%3EHow%20It%20Works%3A%20sys.dm_tran_session_transactions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-316802%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Jan%2024%2C%202012%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20For%20some%20reason%20I%20have%20been%20looking%20at%20DMV%20output%20closely%20the%20last%20couple%20of%20weeks.%26nbsp%3B%26nbsp%3B%26nbsp%3B%20I%20just%20blogged%20about%20the%20pending%20I%2FO%20requests%20and%20now%20I%20have%20a%20behavior%20to%20outline%20for%20%3CEM%3E%20dm_tran_session_transactions%20%3C%2FEM%3E%20for%20todays%20blog.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBLOCKQUOTE%3Eselect%20*%20from%20sys.dm_tran_session_transactions%3CP%3E%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EThe%20scenario%20I%20was%20looking%20at%20is%20as%20follows.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CTABLE%3E%0A%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CSTRONG%3E%20Server%20A%20%3C%2FSTRONG%3E%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CSTRONG%3E%20%3C%2FSTRONG%3E%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CSTRONG%3E%20Server%20B%20%3C%2FSTRONG%3E%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%3CTD%3EBroker%20calls%20activation%20procedure%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%3CTD%3EBegin%20Tran%20(local)%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%3CTD%3ELinked%20Server%20Query%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3ETransaction%20Promoted%20to%20DTC%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3ETransaction%20Imported%20from%20Server%20A%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%3CTD%3Exp_cmdshell%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3ELooped%20back%20and%20blocked%20%3CBR%20%2F%3E%20Separate%20Transaction%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3EBlocking%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%0A%20%20%20%3CLI%3EThe%20process%20is%20based%20on%20SQL%20Service%20Broker.%3C%2FLI%3E%0A%20%20%20%3CLI%3EThe%20service%20broker%20session%20on%20Server%20A%20is%2020%20%3CSTRONG%3E%20s%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%0A%20%20%20%3CLI%3EIt%20starts%20a%20local%20transaction%20and%20eventually%20performs%20a%20linked%20server%20query.%3C%2FLI%3E%0A%20%20%20%3CLI%3EThis%20causes%20the%20transaction%20to%20be%20promoted%20to%20a%20DTC%20transaction.%3C%2FLI%3E%0A%20%20%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20At%20this%20point%20in%20time%20the%20*active%20transaction*%20DMVs%20on%20both%20Server%20A%20and%20B%20show%20the%20enlisted%20UOW%20of%20the%20DTC%20transaction.%20%3CEM%3EThat%20is%2C%20except%20the%20sys.dm_tran_session_transactions%20on%20server%20A.%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20After%20some%20digging%20I%20uncovered%20that%20the%20dm_tran_session_transactions%20DMV%20only%20outputs%20rows%20for%20sessions%20(%20%3CSTRONG%3E%20s%20%3C%2FSTRONG%3E%20)%20that%20are%20NOT%20system%20level%20sessions.%26nbsp%3B%26nbsp%3B%20Since%20the%20broker%20activity%20is%20handled%20on%20a%20system%20session%20the%20DMV%20will%20not%20materialize%20a%20row%20for%20Session%2020%20%3CSTRONG%3E%20s%20%3C%2FSTRONG%3E%20on%20Server%20A%20in%20this%20example.%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Instead%20you%20have%20to%20use%20the%20additional%26nbsp%3B%20*active%20transaction*%20tables%20to%20track%20the%20UOW%20across%20this%20system.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3ENote%3A%20%3C%2FSTRONG%3E%20I%20am%20able%20to%20use%20any%20transaction%20(local%20or%20DTC)%20as%20part%20of%20SQL%20Service%20Broker%20activation%20which%20will%20not%20show%20rows%20in%20the%20session%20transactions%20DMV%20because%20it%20is%20considered%20a%20system%20session.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EBob%20Dorr%20-%20Principal%20SQL%20Server%20Escalation%20Engineer%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-316802%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jan%2024%2C%202012%20For%20some%20reason%20I%20have%20been%20looking%20at%20DMV%20output%20closely%20the%20last%20couple%20of%20weeks.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-316802%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ETransactions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETroubleshooting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 02:12 PM
Updated by: