I received a request from my development team for a query which is stuck and not giving any result and no error throwing.
I thought for a moment and start troubleshooting by checking the blocking. I caught the issue as blocking was there. The major blocking spid was -1.
I prepared a solution of this problem for everyone by collecting the information from different sources.
What is -1 spid?
SPIDs with a value of -1 are orphaned distributed transactions. It is a distributed transaction for which the transactional state is unknown. A distributed transaction is a database transaction that involves more than one database system (usually) located on different servers.
These negative values may be -1, -2 etc.
The process responsible for coordinating these transactions is the Microsoft Distributed Transaction Coordinator, or MSDTC.
How to Kill Negative SPId:
Open a new query window and execute the script:
select * from sys.syslockinfo
You will get the result as below:
Here, need to check the UOW (Unit of work), it would be a 24 character long id, Copy the UOW id and then kill it.