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.