Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Kill a Blocking Negative SPID in SQL Server


By:   |   Read Comments (2)   |   Related Tips: More > Locking and Blocking

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.



Last Update:


signup button

next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

very helpful article 

 


Learn more about SQL Server tools