MSDTC and sys.dm_tran_active_transactions

dbcc opentran


go

dbcc inputbuffer(spidnumber)

go


select distinct(request_owner_guid) as [UoW ID] from sys.dm_tran_locks where request_session_id = -1

--- SQL Server 2005 onwards
select * from sys.dm_tran_active_transactions where name = 'DTCXact'
go
select * from sys.dm_tran_active_transactions where Transaction_state = 2 and name ='DTCXact'


Reference:

http://www.sqlserver-dba.com/2011/11/ms-dtc-and-sysdm_tran_active_transactions.html

http://www.sqlserver-dba.com/2011/07/sql-server-detected-a-dtcktm-in-doubt-transaction-with-uow.html

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.