How come I see a negative SPID and I can not kill it! An inside scoop!

Most often (though this is not common) I find this when doing some performance troubleshooting. A SPID is blocking bunch of other SPID and annoyingly enough, this blocker SPID is reporting a negative value (to be precise, it is -2). As expected, no one have any clue what is it and when tries to kill it, SQL Server returns the following error message:




Msg 6101, Level 16, State 1, Line 1
Process ID -2 is not a valid process ID. Choose a number between 1 and 2048



So what is it and how to fix it? The most burning part of course is “How to fix it?” Click here for a direct step by step solution….



Root cause of this issue is an orphaned distributed transaction. When a transaction is happening between multiple servers, MSDTS comes in place and it does it job well. But in a rare scenario (may be for any type of failure in network), when MSDTC can not make sure a transaction is complete, SQL server changes the related the SPID (related to that particular distributed transaction) to a Negative SPID (-2 to be more precise). So this is expected behavior of SQL Server engine. However as this transaction can still hold some resource (which means even it can be the reason for blocking also), it is best to kill at as soon as it is detected.



Only bad news is that KILL command can not kill it directly because it does not expected a negative value as input parameter. I hope sometime in future Microsoft will enhance it but till now hopefully the recommended workaround as per above link can help us.