How to Kill a Blocking Negative SPID in SQL Server

By:   |   Comments (5)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Locking and Blocking


Problem

A recent issue we faced was SQL Server blocking by a session that had a negative SPID value. When we ran sp_who2 the SPID value that was blocking was (-2). When we tried to KILL the session we got an error that this is not a valid SPID number. So, what are these negative sessions and how can we kill the session in order to break the blocking chain?

Solution

It's common to track SQL Server blocking using sp_who2 and DMVs, so we can find the cause of blocking and then decide to kill the session if necessary. But getting a negative SPID as the source of blocking is not that simple to deal with. When you find that your session is blocked by negative session you are unable to kill it, as the KILL command requires a positive SPID value.

Negative SQL Server SPIDs

Before we know how to deal with this negative sessions, we need to know what they are and how they are caused. There are different negative values and in this tip we will focus on the -2 valued SPIDs.

A session with SPID equal to -2 is an orphaned distributed transaction. A distributed transaction is a database transaction that calls databases hosted on different servers. Orphaned, also called in-doubt MSDTC transactions, are a Microsoft Distributed Transaction with unknown transactional state, as the MSDTC service is not able to identify it correctly due to a MSDTC service crash or unplanned restart.

Using Component Services to Abort In Doubt Transactions

The first thing to troubleshoot is MSDTC itself. Go to the Component Services by typing dcomcnfg in the windows run box on your server.

Inside the Component Services, browse to Computers | My Computer | Distributed Transaction Coordinator as shown below. Then find the appropriate DTC service. Below you can see there are several running on this server, both Local DTC and Clustered DTC. Once you have the correct DTC, go to Transaction Statistics and look at the In Doubt transactions in the Current section as shown below:

In Doubt number

If you find an In Doubt value rather than 0, go to the Transaction List where you will see these marked as In Doubt transactions by a question mark beside it. Right-click on this transaction and select Resolve | Abort as shown below. This will abort this process in the case that the transaction is still visible in the MSDTC GUI and should terminate the session in SQL Server. Also, note the Unit of Work ID which we will use below.

In Doubt value

Kill Negative SQL Server SPID in Management Studio

But what if you don’t find any In Doubt transactions or the process remains running in SQL Server?

In this case, the transaction is still in progress, but MSDTC is not aware of it as it loses track of the transactions, so it will not appear as an internal process. Now we have to use SQL Server to handle it, as the transaction is still accessing resources and blocking database resources. The blocking will not stop as SQL Server has no information if it should commit or roll back the transaction.

The old way to resolve such issues was to restart SQL Server, but that solution is like burning a home in order to kill a bug. We need a way to kill this session only, but we can’t use its SPID as it is negative.

In order to kill these negative SPIDs, we need to use the Unit of Work ID which is a 24-character GUID that is assigned to the transaction by MSDTC. What makes this value useful is that it is common between the operating system and the SQL Server engine.

In order to retrieve this value, we will query the sys.dm_tran_locks system table for the sessions where the request_session_id equals -2 using the below query:

USE Master;
GO

SELECT 
    DISTINCT(request_owner_guid) as UoW_Guid
FROM sys.dm_tran_locks
    WHERE request_session_id =-2
GO

Once we have the GUID, you can use this value with the KILL statement as follows:

KILL 'EB159BBE-B3D5-4F25-971A-F221F09188E1' -- replace GUID value with UoW_Guid value from above query

This should kill the session and now the sessions are no longer blocked and the orphaned transaction is stopped.

Next Steps

If you see this type of blocking occurring it is better to be proactive and find out why these are occurring. Check the application code with the development team and keep an eye on the MSDTC service to know the cause of such crashes.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, April 12, 2023 - 7:57:24 AM - mohan sahu Back To Top (91095)
we can also restart DTC service to kill negative SPID blocking.

Monday, November 9, 2020 - 12:27:45 PM - Miguel Luis Back To Top (87776)
very helpful article thanks a lot!!!

Tuesday, October 29, 2019 - 6:49:58 AM - Monty Back To Top (82923)

Hello,

I have a SQL Server where in we have a case daily where we get a -2 session which causes further blocking.

I checked the in doubt transactions but the count was 0.

How do i analyze and get to the root cause of it.


Sunday, February 14, 2016 - 1:33:29 AM - MusicMan Back To Top (40674)

Anytime I've had to kill a SPID ( > 0) the results can be a bit unpredictable. I can hardly wait to kill my first negative spid but at least I know how to do it now. Thanks for a good article.

 


Monday, January 25, 2016 - 11:16:45 AM - yogi Back To Top (40494)

very helpful article 

 















get free sql tips
agree to terms