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'
/*Returns: 
Transaction_id = “1770489134”
Name = “DTCXact”
Transaction_begin_time =”2011-10-29 11:01:39.517”
Transaction_type = “4”
Transaction_uow=”922D5B60-A5CA-4C96-8891-703021B40AD8”
Transaction_state=”2”
Transaction_status=”12”
Transaction_status2 =”451
Dtc_state=”0”
Dtc_status=”0”
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)