Azure SQL: Negative SPID = -5

Question

i have AzureSQL database (Provisioned - 2vCore, General Purpose) and sometimes my queries are blocked by session with SPID = -5.

I didnt find anything about this negative SPID and how to avoid blocking my queries.

Thanks for any info and help

Answer 1

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