We have some active transactions showing up in MSDTC that have been there for a very long time and don't go away. They're not orphaned or in-doubt, just normal-looking active transactions.
We're trying to figure out what they are and why they're there.
To that end, I'm trying to find a way to map one of these active transactions that are being presented in MSDTC Transaction List (Component Services -> My Computer -> Distributed Transaction Coordinator -> Transaction List) to any information at all in MS SQL Server.
Obviously, all I have to work with is a UOW ID.
After several hours of research, I can't find anything outside of sys.dm_tran_locks that would have this info. The problem I'm having with sys.dm_tran_locks is that my transactions do not show up, presumably because they don't currently hold any locks.
Other important info: SQL Server 2005. (Yes, 2005 :( ) Again, nothing is "orphaned" or "in doubt". The transactions just show as active, but they stay there forever. There is no clustering or anything else exotic. It's all very plain-vanilla SQL Server 2005 standard edition.
EDIT: I should also note that I tried:
* SP_WhoIsActive (both with default settings and with more info gathering turned on) * sys.dm_tran_locks * sys.dm_exec_sessions * sys.dm_exec_requests. * sys.dm_tran_database_transactions * sys.dm_tran_database_transactions * sys.dm_tran_active_transactions
I'm beginning to think you just can't get there from here ;-) Again, I want to associated a UOW in MSDTC to a UOW in SQL Server where, apparently, the distributed transaction is still active but holding no locks.
As requested, here is a screenshot of MSDTC: