Ophaned Users Blocking

The below code should generate the process ID that we required to kill.

select 'kill "' + cast(request_owner_guid as varchar(100)) + '"' from sys.dm_tran_locks

where request_session_id = -2

Reference : 

http://blog.consultdba.com/2012/11/how-to-kill-negative-spid-like-spid-2.html

http://blog.waynesheffield.com/wayne/archive/2012/08/dealing-with-negative-session-ids/

Rarely this scenario will arise when most likely you see this negative SPID (most likely SPID -2) is blocking other transaction causing issues. If you try to kill it using normal KILL command, it will fail reporting below error:

Msg 6101, Level 16, State 1, Line 1

Process ID is not a valid process ID. Choose a number between 1 and 2048 

This is because of an orphaned distributed transaction ID. 

Please follow below steps to kill it:

Step 1: -- Find the UOW Number

select req_transactionUOW

from master..syslockinfo

where req_spid = --  is -2 most likely.

Step 2: -- Copy the UOW number from Step one

KILL ‘’

This will kill the negative SPID resolving the issue. 

There is another way to terminate the transactions which has the status of doubt.

Start run

type dcomcnfg

Under My Computer, click on the Distributed Transaction Coordinator folder. Under Local DTC you will find the following information about MSDTC transactions:

•             Transaction List lists active distributed transactions

•             Transaction Statistics reports summary statistics for previous MDTC transactions

However please note following points:

1. For SPID -2, you may find multiple UOW numbers. Please start killing them one by one. Typically killing first UOW will resolve the issues. (ie. will kill all UOW and release the blocking)

2. If you do not find any valid UOW (In very rare case, you may find all UOW contain only 0 for SPID -2 and you can not kill them.), you may have to restart SQL Service and MS DTC Service for resolving the issue. 

Long Term Fix: You should engage application team to figure out why orphaned distributed transaction ID related issue is generating and request them to fix the code as needed.