WikiDBA

It’s very rare to see negative SPID as its not a common things, it may be you have been performing an investigation in SQL Server into a performance issue related to some database blocking issue with as usual sp_who or sp_who2 and you see there SPID as -2 or -3. As a resultant top of a blocking chain with a dozen other transactions behind it trying to acquire resource. Until it’s killed, nothing will happen, but problem is – how to kill negative SPID? If you use KILL -2 it will through an error message as

Msg 6101, Level 16, State 1, Line 1
Process ID -2 is not a valid process ID. Choose a number between 1 and 1024.
( SPID -2 is an orphaned DTC transaction & SPID -3 is a deferred transaction)

The negative SPID is known as a distributed transaction SPID or an orphaned distributed transaction SPID. Negative SPID happened when someone has been killed a distributed transaction SPID. An orphaned distributed transaction SPID to be totally precise. Something that involves MSDTC – The Microsoft Distributed Transaction Coordinator. It’s needed to work on two or more servers at a time and fetch data from many those servers within a transaction, means when a transaction involves data that resides on more than one server, such as when a database record is replicated out to two or more servers, MSDTC needs to become involved.

Though, sometimes, all does not go as well as it should. A server drops off the network or there is a power outage at the distributor server. Something messy which servers are not very good at dealing with, MSDTC usually handles these scenarios very well, ensuring that the rules involving the database ACID properties are adhered to do that everything stays in sync which should, and everyone’s happy about what data is in their tables.

But when MSDTC can’t recover from one of these scenarios, the SPID which is handling the distributed transaction on one or more servers cannot do any more work. The result is an orphaned SPID.

In order to mark this as an orphaned, distributed transaction SPID, SQL Server changes the SPID from a positive number to -2. The only problem is, the SPID may still be holding on to resources like table, page or row locks, and blocking other SPIDs which want to access that database object, But because the KILL command can not handle SPIDs with a value of less than 1, you can not use it to kill a negative SPID, such as this. Hence the need to look up the UOW (Unit of Work) ID of the offending SPID before the process can be terminated. To get UOW values, just use the command as

Select req_transactionUOW from master..syslockinfo where req_spid = –2

Noted down the UOW values, This will return a 32 digit UOW number like ‘FA4A8712-12D5-11D3-7213-C12345ABC7E9’. Use this UOW to kill the main blocker as

KILL ‘FA4A8712-12D5-11D3-7213-C12345ABC7E9’

Hopefully now everything being great!