Daily SQL Articles by email:
Get Your Own Blog
If you would like to blog on SQLServerCentral.com then send an email to firstname.lastname@example.org.
Contact the author
Archives for this blog
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 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
you need to kill the above 3 UOW
This should clear all SPID -2 blocking and any orphan transactions in MSDTC.