CODEWITHSUNDEEP
sql-serverlocking
Atle
Atle

Reputation: 571

sql_who2 gives -2 in the BlkBy column

We had a problem today on our dev server where a process locked one table, stopping all other queries on that table to work. When running sql_who2 the blkBy columns value was -2, but the -2 was not in the process list.

When running DBCC INPUTBUFFER (-2) I got the exception Invalid SPID -2 specified.

When running beta_lockinfo I see all the tables and indexes it locks, but all other information is null, except that the ownertype was Transaction.

When restarting the webapp(did a change in web.config) the lock dissapered and the same did all the traces of the -2 SPID

My question is why is the SPID a negative value and why can I not get any info about it?

Upvotes: 0

Views: 5195

Answers (1)

The Lazy DBA
The Lazy DBA

Reputation: 1522

A SPID of -2 represents that your process was blocked by an orphaned distributed transaction.

You can use KILL 'GUID-OF-THE-UOW' to terminate orphaned distributed transactions, or any other distributed transaction. To do this you'll need to get the GUID of the Unit of Work (UOW) 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. The session ID of -2 just makes it easier to identify orphaned transactions by querying the session ID column in the dynamic management views.

Upvotes: 3

Related Questions