:::: MENU ::::

SPID -2 in the instance

Sometimes you come upon an instance with some sessions that have a sessionid < 0, they have -2.

A -2 spid is an orphaned session, that means a session that no longer have an outer connection, maybe a DTC transaction that went wrong or something else, whatever the reason the problem is that these will hang around for a long while and if they are locking up resources, those resources will never be open again until we either kill the -2 spid or restart the instance.

But we can’t use the kill command for this, it won’t work. And how do we find them?

First, finding them, run:

SELECT request_owner_guid 
FROM sys.dm_tran_locks 
WHERE request_session_id = -2 
and request_owner_guid <> '00000000-0000-0000-0000-000000000000'

they will probably need to be killed, but I leave the decision for you to consider, do your homework before just killing them, I’m showing you how, not saying you should do it blindly.

To kill them use the UOW number, the long alphanumeric number instead of sessionid, eg.:

KILL 'DEF12078-0199-1212-B810-CD46A73F2498'

Or if you have a lot:

SELECT 'kill ''' + convert(varchar(100), request_owner_guid) + '''' 
FROM sys.dm_tran_locks 
WHERE request_session_id = -2 
and request_owner_guid <> '00000000-0000-0000-0000-000000000000'
order by 1

After taking care of these, talk to the application/web group to find out how you can fix this problem.

//Richard


So, what do you think ?