Help! I have -2, -3, or -4 Session ID!
We can kill a session by using KILL command. However, KILL command requires a positive number; executing KILL with negative number returns an error:
Msg 6101, Level 16, State 1, Line 1
Session ID -4 is not valid.
In order to kill the session ID, you need to find the unit of work (UOW) guid.
SELECT DISTINCT(request_owner_guid) AS UOW
FROM sys.dm_tran_locks
WHERE request_session_id IN (-2,-3,-4)
Now you can kill this using UOW:
KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'
Like all normal transactions, killing a session causes any work performed by it to be rolled back to bring the database back into consistent state.
The negative session ID are orphaned or stuck sessions that SQL Server; they are rare occurrences. Most often the only one I have seen is -2; what do they mean?
| Session ID | Description |
|---|---|
| -2 | The blocking resource is owned by an orphaned distributed transaction. |
| -3 | The blocking resource is owned by a deferred recovery transaction. |
| -4 | Session ID of the blocking latch owner could not be determined due to internal latch state transitions. |
Reference: Books Online, sys.sysprocesses (Transact-SQL)
This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.