Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLTechnet

Vinoth is currently a Senior SQL Server DBA and 10 years of experience as SQL Server DBA. Started my career as a DBA in SQL 6.5/7 has worked in all subsequent SQL Server version. Vinoth has worked in some of the largest SQL server environments in the world in various domains ranging from Finance, Retail, Manufacturing, Consulting, Web etc. Vinoth has Engineering Degree in Computer Science and has certified in MCITP - Database Adminstrator in 2008/2005, MCDBA and ITIL Foundation V3.

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.

Comments

Leave a comment on the original post [www.sqltechnet.com, opens in a new window]

Loading comments...