Blocking caused by -1/Negative SPID

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.

Solution:

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:

UOW (Unit of work)

 

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.

Kill ‘CD947343-36F5-4515-B4CB-3A42402C8207’

Thank You!

Advertisements

About Mirza Husain

Mirza Husain is a senior consultant-database and having more than 10+ years of experience in the IT industry with different domains. He is fond of writing and speaking about SQL Server and also keen to learn new technologies. He is holding MCA degree and having Microsoft certifications as MCP & MCTS. He is also ITIL certified. In his past years, he worked with many clients as Microsoft, Bank of America etc. Currently he is associated with a France based client and offering his best services. You may reach him on his email id -- mirza_dba@outlook.com. Thanks!
This entry was posted in Database Administration, Sql Server, Uncategorized. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s