Negative Number in SPID-CodePudding

I am constantly getting Azure SQL Server sessions with (blocked by) "Blk By" column of -5.

What could this session be please? I have searched for negative number SPID and I can see information for -2, -3, -4 but not for -5.

(I have removed identifiable information for hostname, login dbname columns)

Copied below is a listing from sp_who2

CodePudding user response：

According to the documentation for blocking_session_id

-5 = Session ID of the blocking latch owner could not be determined because it is not tracked for this latch type (for example, for an SH latch).

CodePudding user response：

Sessions with negative SPID are probably orphaned transactions. You cannot kill the session using KILL command as it needs a positive SPID number. Try running below query on the Azure SQL Database:

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

This should return a GUID like with the following format: 00000000-0000-0000-0000-000000000000

Try to kill the session using the GUID instead of the SPID as shown below:

KILL '00000000-0000-0000-0000-000000000000' -- replace GUID value with UoW_Guid value from above query

On the management view named in sys.dm_exec_requests you may be able to see the wait_type that is involved in the blocking.