Explanation:
This example is of an Orphaned Distributed Transaction SPID which specifically involves MSDTS, the Microsoft Distributed Transaction Coordinator. The is what happens when a transaction involves data which resides on more than one server (such as when a database record is replicated out to two or more servers where MSDTC needs to become involved) and the following happens: a) A server drops off the network
b) There’s a power outage at the distributor server.
This example is of an Orphaned Distributed Transaction SPID which specifically involves MSDTS, the Microsoft Distributed Transaction Coordinator. The is what happens when a transaction involves data which resides on more than one server (such as when a database record is replicated out to two or more servers where MSDTC needs to become involved) and the following happens: a) A server drops off the network
b) There’s a power outage at the distributor server.
The Negative SPID (-2) in SQL Server
Phenomenon: May be you have been investigating a performace issue in SQL Server or exploring the error log file for sometime. But to add to your astonishment you notice that there is a negative SPID = -2 at the top of the chain and is the cause of all deadlocks.
What next?
Definitely you would like to wait (not sure how long?) or try to kill the process.
Here is what you have when you try any of them:
Wait: No use waiting as the process will not complete/refresh untill a server restart.
Kill: Yes. Ofcourse you can try to kill the process by executing KILL -2. Here is your prize:
Msg 6101, Level 16, State 1, Line 1Process ID -2 is not a valid process ID. Choose a number between 1 and 2048
Anything else in Pandora's box?
No...?
Solution:
Try this.
SELECT req_transactionUOWFROM master..syslockinfo
WHERE req_spid = -2
WHERE req_spid = -2
This will return a 32 digit UOW number which looks like a GUID. Something like ‘BCD12078-0199-1212-B810-AB46A24F2498’
Run the KILL command with the above UOW.
Run the KILL command with the above UOW.
KILL ‘BCD12078-0199-1212-B810-AB46A24F2498’
Now run sp_who2/sp_who. To your surprise its SPID -2 is GONE.
Comments (0)
Post a Comment