By: Ahmad Yaseen | 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:
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.
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.
- Read more about Troubleshooting SQL Server Distributed Transactions.
- Check out the SQL Server Locking and Blocking Tips.
About the author
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
