Moslem Ben Dhaou Moslem Ben Dhaou -4 years ago 112
SQL Question

Negative SPID in SQL Server?

I am trying to find and kill a session that has set a lock on one of the table and is stopping many other queries from executing. For that I am using the following script:

SELECT REQUEST_MODE, REQUEST_TYPE, REQUEST_SESSION_ID
FROM sys.dm_tran_locks
WHERE RESOURCE_TYPE = 'OBJECT'
AND RESOURCE_ASSOCIATED_ENTITY_ID =(SELECT OBJECT_ID('System'))


The result set I get is as follow:

enter image description here

Running
EXEC sp_who2
does not return any row with a negative SPID. How can I find the session locking my table?

Answer Source

sys.dm_tran_lock, REQUEST_SESSION_ID:

A value of -2 indicates that the request belongs to an orphaned distributed transaction.

So, no actual session ID any longer exists here. Reading the remarks should lead you to KILL:

Use KILL UOW to terminate orphaned distributed transactions. These transactions are not associated with any real session ID, but instead are associated artificially with session ID = '-2'. This session ID makes it easier to identify orphaned transactions by querying the session ID column in sys.dm_tran_locks, sys.dm_exec_sessions, or sys.dm_exec_requests dynamic management views.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download