SQL session blocked by a negative SPID (-2)

A couple of times now I have faced this issue with my clients where SQL Server session is blocked by another session that has 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.

On research we figured out that, 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.

To rectify this issue, we can initiate the troubleshooting from DTC console.Go to the Component Services by typing dcomcnfg in the windows run box on the 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.

If if in 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 to get rid off this scenario we may have to restart the SQL services to clear all the blocked sessions.

Instead we also have a workaround, where we find the Unit of Work ID which is a 24-character GUID that is assigned to the transaction by MSDTC and use this UoW_ID to kills the negative SPID.

The below query can be used to retrieve the UoW_ID value :

USE Master;
GO

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

Post which using the KILL '<UoW_Guid>'  query, we can kill all the UoW_ID those which gets listed as the outcome of the above query. And thus we can release our SQL transaction to proceed without any hurdles or blocks.

Hope it helps!! Happy releasing!!