Negative blocking session ids

~ franklinyamamoto

On occasion while examining lock scenarios, I’ve seen a lead blocker with a negative session ID.  After looking in the documentation for the blocking_session_id, it explained why I am seeing this (taken directly from Microsoft’s sys.dm_exec_requests documentation):

  • -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.
  • -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).

Session ID -2

This indicates that session id -2 is likely a problem and you will need to kill the orphaned distributed transaction.  You cannot kill -2.  Instead you need to find the UOW and kill it by the GUID:

Select req_transactionUOW
from sys.syslockinfo
where req_spid = -2

Then issue a kill on the UOW:

KILL ‘DD3C1422-2967-4356-6712-C08732DF1714’

Session ID -3

A session ID of -3 indicates this session is in a deferred recovery transaction.  This indicates a block is occurring because a page being accessed by your query was possibly damaged.  This is something that probably needs to be researched.  Check the wait_resource column of sys.dm_exec_requests to see which page is at issue.

Session ID -4

A session ID of -4 is probably not an issue.  It may have been timing of when the query to sys.dm_exec_requests ran that this would show a -4.

Session ID -5

A session ID of -5 likely is not an issue.  This is going to show up for a lot of things such as a session that is waiting on a PAGEIOLATCH_SH (This is a physical IO wait).  I happened to see this occurring and decided to research this and wrote this post as a result:

Based on the documentation, session ID -5 seems to be something that shows up in newer versions of SQL Server.  The documentation wasn’t clear on what version this was introduced.

In short, if you see a block on session id -5, don’t focus on the block – focus on the wait type that the session was encountering.  In the example above fixing slow disk IO would help alleviate the performance problem.  To be honest, I’m not wild that Microsoft introduced this – it is misleading and will have people chasing red herrings.

Final thoughts

A negative session ID is a session that does not really exist.  It is a placeholder for workload that does not have a session ID associated with it.  Some negative session IDs are just noise while others indicate a problem.  Happy tuning!