Kill Negative SPID -2 in SQL Server 2014

There can be a rare scenario when database restore or a query is being blocked by negative SPIDs when trying to find the lead blocker by running SP_WHO2 or using Dynamic Management Views. Negative SPIDs in SQL Server cannot be killed using KILL Command. This article explains how to kill Negative SPIDs in SQL Server and what they mean in SQL Server.

Source: Kill Negative SPID in SQL Server – MyTechMantra.com

I had an ugly case of persistent blocking on a sunday morning at 7 AM. I had database blocking going on with -2 SPID as lead blocker. -2 is an orphaned MS DTC transaction.

You cannot just do a

KILL -2

I found out as much information as I could about the offending query for blaming purposes 😉 like which database this is happening on using the resource_database_id from request_session_id etc. Now its time to kill the offending DTC orphan transaction.

Identify the offending transaction:

SELECT 
 DISTINCT(request_owner_guid)
FROM sys.dm_tran_locks
 WHERE request_session_id IN (-2)
GO

guess what I had multiple Unit of Work IDs (UOW):

00767C6D-5981-4488-B151-43E0A2DC5C31
3AFCD1C4-581E-4F68-A11F-5D8C6B5EABB4
B36C240B-EEF5-41A1-9863-C4DD8C313F8A
D826E956-39ED-45FB-9B57-DBF5DB236644
185E7533-6532-4639-A023-EC0371EE952E

and this how I killed’em all:

kill '00000000-0000-0000-0000-000000000000'
--The distributed transaction with UOW {00000000-0000-0000-0000-000000000000} does not exist.
kill '00767C6D-5981-4488-B151-43E0A2DC5C31'
kill '3AFCD1C4-581E-4F68-A11F-5D8C6B5EABB4'
kill 'B36C240B-EEF5-41A1-9863-C4DD8C313F8A'
kill 'D826E956-39ED-45FB-9B57-DBF5DB236644'
kill '185E7533-6532-4639-A023-EC0371EE952E'

Once I killed the orphaned -2 things got back to normal and the application started behaving normally.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s