Identify blocking and blocked sessions

A request remains suspended and returns an identifier in blocking_session_id. We need to know who is waiting, what session is ahead, and what text we can inspect at both ends.

What we need to distinguish

Blocking occurs when one session retains protection over a resource another session needs. The blocker owns or retains that protection; the blocked session waits to continue.

We will show the blocked request and the session marked as blocking. If the latter does not have an active request, we will retrieve the most recent text of its connection as a clue, making it clear that it may not be the instruction that opened the transaction or acquired the lock.

Query

/*
    Antes de ejecutar:
        Revisa los permisos y los límites antes de ejecutar la consulta.
        Confirma el alcance, la base activa y el costo posible de la consulta.
        Captura evidencia en varios momentos antes de atribuir una causa.

    Este script es de observación. No aplica correcciones ni modifica datos.
*/

SET NOCOUNT ON;
GO
-- Consulta
SELECT
    rb.session_id AS SesionBloqueada,
    rb.blocking_session_id AS SesionBloqueadora,
    sb.login_name AS LoginBloqueado,
    sb.host_name AS EquipoBloqueado,
    sb.program_name AS AplicacionBloqueada,
    sr.login_name AS LoginBloqueador,
    sr.host_name AS EquipoBloqueador,
    sr.program_name AS AplicacionBloqueadora,
    rb.wait_type AS TipoEspera,
    rb.wait_time AS EsperaMilisegundos,
    rb.wait_resource AS RecursoEspera,
    SUBSTRING
    (
        tb.text,
        (rb.statement_start_offset / 2) + 1,
        ((CASE rb.statement_end_offset
              WHEN -1 THEN DATALENGTH(tb.text)
              ELSE rb.statement_end_offset
          END - rb.statement_start_offset) / 2) + 1
    ) AS SentenciaBloqueada,
    tr.text AS TextoActualORecienteBloqueador
FROM sys.dm_exec_requests AS rb
INNER JOIN sys.dm_exec_sessions AS sb
    ON sb.session_id = rb.session_id
LEFT JOIN sys.dm_exec_sessions AS sr
    ON sr.session_id = rb.blocking_session_id
LEFT JOIN sys.dm_exec_requests AS rr
    ON rr.session_id = rb.blocking_session_id
LEFT JOIN sys.dm_exec_connections AS cr
    ON cr.session_id = rb.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(rb.sql_handle) AS tb
OUTER APPLY sys.dm_exec_sql_text
(
    COALESCE(rr.sql_handle, cr.most_recent_sql_handle)
) AS tr
WHERE rb.blocking_session_id IS NOT NULL
  AND rb.blocking_session_id <> 0
ORDER BY
    rb.wait_time DESC,
    rb.session_id;
GO

Parameters or permissions

The queried dynamic management views (DMVs) are server-scoped and expose state that changes as the engine works. VIEW SERVER STATE is normally needed; since SQL Server 2022, VIEW SERVER PERFORMANCE STATE. With Multiple Active Result Sets (MARS), a single connection can maintain more than one active request and appear in various combinations. SQL Server also uses negative values in blocking_session_id for owners that cannot be represented as a normal session, such as certain distributed transactions or internal states. In those cases there will be no login data or text for the supposed blocker.

How to read the result

Each row starts from a waiting request. BlockingSession is the identifier communicated by SQL Server, while WaitResource describes the resource in DMV format; none yet explains why the blockade was prolonged. If the blocking session is active, CurrentTextORRecentBlocker usually corresponds to your current request. If inactive, the text comes from most_recent_sql_handle and only shows the latest associated with the connection.

What this does establish

It allows you to match blocked requests with their blocker ID and gather session, hold, and text data available during capture.

What this still does not establish

It does not prove that the recent text has acquired the lock or that logging out is a safe action. Nor does it by itself show the entire chain or the extent of a possible reversal.

Next reasonable check

Build the complete blockchain. If the head is idle, review the open transactions and idle sessions with a transaction before deciding on any intervention.

Search SQL Server