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
WHERE RESOURCE_TYPE = 'OBJECT'
AND RESOURCE_ASSOCIATED_ENTITY_ID =(SELECT OBJECT_ID('System'))
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
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.