0

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: MSDTC Screen Shot

Improve this question
7
  • Sp_whoisactive will help you.session ID column in sys.dm_tran_locks, sys.dm_exec_sessions, or sys.dm_exec_requests dynamic management views.
    – Kin Shah
    Commented Aug 11, 2014 at 22:58
  • Thanks :-). I should have noted in the original post that I tried Sp_whoisactive with default settings and did not get the UOW ID. I suppose I will go back and tweak the settings and try again. sys.dm_tran_locks showed nothing, but I assumed it was because the transaction was not holding locks. I'll look into exec_sessions and exec_requests.
    – Joe Hayes
    Commented Aug 12, 2014 at 15:18
  • one more thing to check req_transactionUOW column from master..syslockinfo to see if you find useful.
    – Kin Shah
    Commented Aug 12, 2014 at 15:24
  • Thanks again, Kin. I think that syslockinfo is pretty much the same as sys.dm_tran_locks. Looks like if I don't have a lock, I just can't get it.
    – Joe Hayes
    Commented Aug 12, 2014 at 15:53
  • Can you post a screenshot of what you are telling - 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.
    – Kin Shah
    Commented Aug 12, 2014 at 17:23

1 Answer 1

Reset to default
1

You can get the correlation you're looking for in the sys.dm_tran_active_transactions DMV. The column is transaction_uow.

http://msdn.microsoft.com/en-us/library/ms174302.aspx

Improve this answer
1
  • I'll check that out!
    – Joe Hayes
    Commented Aug 22, 2014 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.