Monday, 6 August 2012

Blocking in SQL Server by SPID -2 (Orphan distributed transactions)


Blocking in in SQL Server by SPID -2 happens due to Orphan DTC transaction, for instance whenever a data source connected in MSDTC is rebooted abruptly when a transaction is active, MSDTC does not recognize that one of its destination has been rebooted and keeps the transactive forever wiating for an acknowledgement, Hence the transaction is neither marked in doubt nor completes. This is when you might have blocking in your SQL server where your lead blocker is -2 and You cannot kill this spid using normaill Kill -2 or you cannot resolve the transaction in MSDTC as the trasaction is active. To clear Orphan MSDTC transaction you must KILL UOW associated with the orphaned distributed transactions by querying the session ID column in sys.dm_tran_locks dynamic management views as below,
 
select DISTINCT request_owner_guid from sys.dm_tran_locks where request_session_id = -2
 
assuming the result of above Query is
 
request_owner_guid
------------------------------------
922D5B60-A5CA-4C96-8891-703021B40AD8
65B40AD8-A5CA-4C96-8891-703021B40AD8
91265B40-A5CA-4C96-8891-703021B40AD8
 
you need to kill the above 3 UOW
 
KILL '922D5B60-A5CA-4C96-8891-703021B40AD8'
KILL '65B40AD8-A5CA-4C96-8891-703021B40AD8'
KILL '91265B40-A5CA-4C96-8891-703021B40AD8'
 
This should clear all SPID -2 blocking and any orphan transactions in MSDTC.

3 comments:

  1. I have an issue with XA transactions where it leaves -2 processes and I was able to kill most of them but there are 2 remaining with all 0's for the UOW and it won't let me kill those. It will take coordination to be able to restart the instance. Is there a way to get rid of those 2 remaining ones do you know?

    ReplyDelete
  2. can you sent me output of dm_tran_locks for your case?

    ReplyDelete
  3. what is resolution on occurrence of Orphan DTC transaction

    ReplyDelete