SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

MS DTC and sys.dm_tran_active_transactions

A  sql reindex command was being blocked by two active transactions in MS DTC.   Use

sys.dm_tran_active_transactions to report more detail on transactions



select * from sys.dm_tran_active_transactions where transaction_uow  = '922D5B60-A5CA-4C96-8891-703021B40AD8'


Transaction_id = “1770489134”	
Name = “DTCXact”
Transaction_begin_time =”2011-10-29 11:01:39.517”
Transaction_type = “4”
Transaction_status2 =”451
Dtc_isolation_level= “1048576”


SQL Server Books Online has full details only all columns and values returned > For analysis purposes , I’ll highlight a few:

 Transaction_type = “4”  means it’s a distributed transaction

Transaction_state =”2”  means the transaction is active

 As a follow up I execute:

select * from sys.dm_tran_locks  where request_owner_guid = '922D5B60-A5CA-4C96-8891-703021B40AD8'

 This returns transactions  related to this owner guid.

If you see -2 in the request_session_id column , investigate further for orphaned distributed transactions.

Related Posts

SQL Server detected a DTC/KTM in-doubt transaction with UOW

Author: Jack Vamvas (http://www.sqlserver-dba.com)


No comments.

Leave a Comment

Please register or log in to leave a comment.