Sql_who2 gives -2 in BlkBy column
We had a problem today on our dev server where a process has locked one table, stopping all other queries on that table. At startup sql_who2
, the blkBy column value is -2, but -2 was not in the process list.
DBCC INPUTBUFFER (-2)
I got an exception on startup , the specified invalid SPID value is -2.
On running beta_lockinfo I see all the tables and indexes that it locks, but all other information is null, except that the owner type was a transaction.
When restarting webapp (there was a change in web.config), the blocking was disabled and all traces are -2 SPID
My question is, why is SPID negative and why can't I get information about it?
a source to share
SPID -2 means that your process was blocked by an orphan distributed transaction.
You can use KILL 'GUID-OF-THE-UOW'
to stop the transfer of lost distributed transactions or any other distributed transaction. To do this, you need to get the Unit of Work (UOW) GUID from sys.dm_tran_locks.request_owner_guid
.
SELECT
[tl].[request_owner_guid]
FROM
sys.dm_tran_locks tl
WHERE
[tl].[request_session_id] = -2
-2 is just an artificial association. Session ID -2 just makes it easier to identify lost transactions by querying the session ID column in dynamic management views.
a source to share