Wednesday 22 February 2017

How to handle orphan transactions in SQL Server


-2 blocking means Orphaned distributed transactions, in this transaction become in-doubt and MSDTC lost control over it, these transactions may or may not be listed in MSDTC (depend upon issues occurred).

uow

In order to find the session that is handling request for this UOW (Unit of Work), refer to below query that will lead to session id which can provide detailed information of activity. There are few ways to release the transaction, one from SQL (Use KILL <UOW> command) and another from MSDTC, right click on UOW and select resolve. I would suggest to cross verify with session_id and UOW before resolving in MSDTC.
 select distinct request_session_id from sys.dm_tran_locks where request_owner_guid='{8241D0EE-364B-4315-A293-D46F9659CAEF}’
 If MSDTC do not list any transaction we can still find with below query. Let me know if any questions.
 select request_owner_guid from sys.dm_tran_locks where request_session_id = -2

No comments:

Post a Comment