How to deal with negative SPIDs in SQL Server?
Some times we found negative SPIDs causing blocking other processes in SQL Server. The source of a negative SPID is -2. So how to kill it?
KILL -2
Msg 6101, Level 16, State 1, Line 1
Process ID -2 is not a valid process ID. Choose a number between 1 and 2048
Kill wont allow you to pass that negative SPID, So how can we do this?
Run the following query
select req_transactionUOW from master..syslockinfo where req_spid = -2
This will return a 24 character GUID (Unit of work ID UOW) number, something like ‘EDF78022-8990-5757-E147-FA85B21C3654’
Kill ‘EDF78022-8990-5757-E147-FA85B21C3654’
After that run sp_who2 that negative spid has disappeared.
What actually that negative SPID is?
Some times we found negative SPIDs causing blocking other processes in SQL Server. The source of a negative SPID is -2. So how to kill it?
KILL -2
Msg 6101, Level 16, State 1, Line 1
Process ID -2 is not a valid process ID. Choose a number between 1 and 2048
Kill wont allow you to pass that negative SPID, So how can we do this?
Run the following query
select req_transactionUOW from master..syslockinfo where req_spid = -2
This will return a 24 character GUID (Unit of work ID UOW) number, something like ‘EDF78022-8990-5757-E147-FA85B21C3654’
Kill ‘EDF78022-8990-5757-E147-FA85B21C3654’
After that run sp_who2 that negative spid has disappeared.
What actually that negative SPID is?
That is a distributed transaction SPID. An orphan distributed transaction SPID that involves Microsoft Distributed Transaction coordinator. In a nutshell a distributed transaction that involves more than one database located on different servers.
In order to maintain transactional consistency these transactions needs to be coordinated, as the transaction involves two phase commit, first it needs to commit on one database and then commit the transaction on second database, if it commit at first database and fails to commit at second database then whole transaction is rolled back and no data gets committed by any database. The process is responsible for coordinating these transactions is Microsoft Distributed Transaction Coordinator or MSDTC
The Negative SPID problem arises for whatever reason MSDTC loses track on one of these transactions, then that transaction will marked as in-doubt. If this happens, SQL Server will lock the SPID bound to that transaction but SPID will be -2.
This means it is no longer an internal process of SQL Server, and SQL Server doesn’t know whether it can commit or rollback so it causes blocking by holding locks on the tables that transaction is accessing. Sometimes it happens when MSDTC is in hung state or terminated.
No comments: