Encontrar sesiones inactivas con transacción abierta

Una sesión aparece como sleeping, pero continúa bloqueando otras solicitudes o impide reutilizar espacio del log. No está ejecutando una petición y, aun así, conserva una transacción.

Qué necesitamos distinguir

sleeping indica que la sesión está conectada pero no ejecuta una solicitud. Una transacción abierta aún puede conservar bloqueos e impedir que el log reutilice registros necesarios para recuperar sus cambios.

Buscaremos sesiones de usuario inactivas asociadas con transacciones abiertas. También mostraremos cuándo terminó su última solicitud y el texto más reciente disponible, sin asumir que ese texto inició la transacción.

Consulta

/*
    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
DECLARE @MinutosInactiva INT = 5;

SELECT
    s.session_id AS IdSesion,
    s.login_name AS InicioSesion,
    s.host_name AS EquipoCliente,
    s.program_name AS Aplicacion,
    s.status AS EstadoSesion,
    s.last_request_end_time AS FinUltimaSolicitud,
    DATEDIFF_BIG
    (
        SECOND,
        s.last_request_end_time,
        SYSDATETIME()
    ) AS SegundosInactiva,
    ta.transaction_id AS IdTransaccion,
    ta.transaction_begin_time AS InicioTransaccion,
    DATEDIFF_BIG
    (
        SECOND,
        ta.transaction_begin_time,
        SYSDATETIME()
    ) AS SegundosTransaccionAbierta,
    st.open_transaction_count AS TransaccionesAbiertas,
    st.is_local AS EsLocal,
    st.is_enlisted AS EstaEnlistada,
    texto.text AS TextoMasReciente
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions AS ta
    ON ta.transaction_id = st.transaction_id
OUTER APPLY
(
    SELECT TOP (1)
        c.most_recent_sql_handle,
        c.connect_time
    FROM sys.dm_exec_connections AS c
    WHERE c.session_id = s.session_id
    ORDER BY c.connect_time DESC
) AS conexion
OUTER APPLY sys.dm_exec_sql_text
(
    conexion.most_recent_sql_handle
) AS texto
WHERE s.is_user_process = 1
  AND s.status = N'sleeping'
  AND st.is_user_transaction = 1
  AND s.last_request_end_time <=
      DATEADD(MINUTE, -@MinutosInactiva, SYSDATETIME())
ORDER BY
    ta.transaction_begin_time,
    s.session_id;
GO

Parámetros o permisos

@MinutosInactiva evita mostrar conexiones que apenas terminaron una solicitud. Ajusta el umbral a la duración normal entre operaciones de la aplicación. Se necesita visibilidad sobre sesiones, conexiones y transacciones del servidor. El conteo de transacciones de sys.dm_tran_session_transactions puede no coincidir con el de sys.dm_exec_sessions en escenarios como transacciones enlazadas o distribuidas; aquí se conserva el valor de la asociación consultada.

Cómo leer el resultado

sleeping significa que la sesión no ejecuta una solicitud en ese instante, no que haya liberado su transacción. Compara FinUltimaSolicitud con InicioTransaccion: una inactividad prolongada después de abrir la transacción merece revisar el manejo de conexión y de COMMIT o ROLLBACK en la aplicación. TextoMasReciente es la última instrucción conocida para la conexión. Puede haber ejecutado varias sentencias desde que comenzó la transacción, por lo que debe tratarse como una pista.

Qué conclusión sí permite obtener

Permite identificar sesiones de usuario inactivas que siguen asociadas con una transacción y medir cuánto tiempo llevan en ese estado observable.

Qué conclusión todavía no permite obtener

No demuestra cuál instrucción dejó abierta la transacción, cuánto tardaría una reversión ni si la conexión pertenece a un proceso que espera legítimamente otra operación.

Siguiente comprobación razonable

Relaciona la sesión con las sesiones bloqueadoras y bloqueadas y con la cadena completa de bloqueo. Después revisa el flujo de la aplicación: apertura de conexión, inicio de transacción, manejo de excepciones y caminos que omiten COMMIT, ROLLBACK o liberación del recurso.

Buscar en SQL Server