CODEWITHSUNDEEP
sqlsql-serverlocking
Moslem Ben Dhaou
Moslem Ben Dhaou

Reputation: 7005

Negative SPID in SQL Server?

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

The result set I get is as follow:

enter image description here

Running EXEC sp_who2 does not return any row with a negative SPID. How can I find the session locking my table?

Upvotes: 2

Views: 4356

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

sys.dm_tran_locks, REQUEST_SESSION_ID:

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 KILL:

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.

Upvotes: 6

Dinesh vishe
Dinesh vishe

Reputation: 3598

how negative traction : Negative SPID happened when someone has been killed a distributed transaction SPID. An orphaned distributed transaction SPID to be totally precise.

Component Services by typing dcomcnfg in the windows run box on server. enter image description here

enter image description here

You can find negative session in ssms using following query.

USE Master; GO

SELECT DISTINCT(request_owner_guid) as UoW_Guid FROM sys.dm_tran_locks WHERE request_session_id =-2 GO

enter image description here

you have kill mention GUID in ssms.

SSMS:- Kill 'GUID'

blocking_session_id ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).

-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 at this time because of internal latch state transitions.

UOW values, This will return a 32 digit UOW number

Upvotes: 1

Related Questions