Explorar guías rápidas · SQL Server

Tecnología: SQL ServerCategoría: Solución de problemasNivel: IntermedioActualizado

Encontrar transacciones abiertas

Identifica las transacciones abiertas, encuentra las sesiones que las mantienen, mide su antigüedad y relaciona las transacciones de larga duración con bloqueos, el crecimiento del log y el comportamiento de la aplicación.

En esta página
  1. Problema
  2. Respuesta rápida
  3. Código / comandos
  4. 1. Listar las transacciones de usuario abiertas por antigüedad
  5. 2. Agregar la solicitud actual y el texto SQL
  6. 3. Encontrar sesiones inactivas con transacciones abiertas
  7. 4. Inspeccionar los detalles de transacción de una sesión
  8. 5. Encontrar sesiones que bloquean a otras y tienen transacciones abiertas
  9. 6. Mostrar la transacción activa más antigua de la base de datos actual
  10. 7. Comprobar por qué está en espera la reutilización del log de transacciones
  11. 8. Inspeccionar el SQL reciente de un propietario de transacción inactivo
  12. Qué significa el resultado
  13. Qué revisar
  14. Transacción muy antigua
  15. Sesión inactiva + transacción abierta
  16. ACTIVE_TRANSACTION en log_reuse_wait_desc
  17. La sesión bloqueadora tiene una transacción antigua
  18. Muchas sesiones con transacciones abiertas
  19. Notas de producción
  20. open_transaction_count es útil, pero no lo dice todo
  21. Una sesión inactiva puede seguir siendo peligrosa
  22. Las transacciones abiertas pueden afectar la reutilización del log
  23. Las transacciones abiertas pueden aumentar el costo del rollback
  24. Las cargas de trabajo con versionado también pueden verse afectadas
  25. Los permisos varían según la versión y la plataforma
  26. Errores comunes
  27. 1. Suponer que las sesiones inactivas son inofensivas
  28. 2. Terminar el bloqueador antes de evaluar el riesgo del rollback
  29. 3. Mirar solo el texto SQL actual
  30. 4. Tratar DBCC OPENTRAN como un inventario completo
  31. 5. Ignorar los límites transaccionales de la aplicación
  32. 6. Suponer que la antigüedad de la transacción por sí sola demuestra un problema
  33. Siguiente paso
  34. Para profundizar

Problema

SQL Server tiene bloqueos, el log de transacciones no se trunca como esperabas, una sesión parece inactiva pero sigue manteniendo bloqueos, o sospechas que una aplicación dejó una transacción abierta.

El objetivo es responder:

  • ¿Qué sesiones tienen transacciones abiertas en este momento?
  • ¿Qué antigüedad tienen esas transacciones?
  • ¿La sesión propietaria está activa o inactiva (sleeping)?
  • ¿Qué estaba haciendo la sesión?
  • ¿Una transacción abierta está bloqueando a otras sesiones?
  • ¿Una transacción antigua podría estar contribuyendo al crecimiento del log de transacciones?

Respuesta rápida

Empieza haciendo un join entre las DMV de sesiones, de transacciones de sesión y de transacciones activas.

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    at.transaction_id,
    at.name AS transaction_name,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,
    at.transaction_type,
    at.transaction_state
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 at
    ON at.transaction_id = st.transaction_id
WHERE
    s.is_user_process = 1
ORDER BY
    at.transaction_begin_time;

Concéntrate primero en las transacciones más antiguas, sobre todo cuando la sesión propietaria está en sleeping o bloquea otro trabajo.

Código / comandos

1. Listar las transacciones de usuario abiertas por antigüedad

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,
    at.transaction_type,
    at.transaction_state
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 at
    ON at.transaction_id = st.transaction_id
WHERE
    s.is_user_process = 1
ORDER BY
    at.transaction_begin_time;

Este es el mejor inventario inicial.

Las transacciones más antiguas merecen atención porque pueden:

  • retener bloqueos
  • retrasar la limpieza de versiones
  • contribuir a problemas de reutilización del log
  • indicar un mal manejo de transacciones en la aplicación

2. Agregar la solicitud actual y el texto SQL

Usa esta consulta cuando quieras saber si el propietario de la transacción está ejecutando trabajo activamente.

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,

    r.status AS request_status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS wait_ms,

    txt.text AS batch_text
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 at
    ON at.transaction_id = st.transaction_id
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    s.is_user_process = 1
ORDER BY
    at.transaction_begin_time;

Importante: una sesión puede tener una transacción abierta aunque no tenga ninguna solicitud activa.

Por eso sys.dm_exec_requests se une con un LEFT JOIN.


3. Encontrar sesiones inactivas con transacciones abiertas

Son especialmente importantes durante los incidentes de bloqueo.

SELECT
    s.session_id,
    s.status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,
    s.last_request_start_time,
    s.last_request_end_time,

    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds
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 at
    ON at.transaction_id = st.transaction_id
WHERE
    s.is_user_process = 1
    AND s.status = N'sleeping'
ORDER BY
    at.transaction_begin_time;

Una sesión inactiva con una transacción antigua puede seguir manteniendo bloqueos.

Esto suele apuntar a:

  • código de aplicación que no hizo commit ni rollback
  • connection pooling con un estado de transacción sin terminar
  • transacciones manuales que quedaron abiertas
  • manejo de errores que omitió la limpieza

4. Inspeccionar los detalles de transacción de una sesión

DECLARE @session_id int = 57;

SELECT
    s.session_id,
    s.status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    st.transaction_id,
    st.is_user_transaction,
    st.is_local,

    at.name AS transaction_name,
    at.transaction_begin_time,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.session_id = @session_id;

Usa esta consulta después de que otro diagnóstico identifique un SPID sospechoso.


5. Encontrar sesiones que bloquean a otras y tienen transacciones abiertas

WITH blockers AS
(
    SELECT DISTINCT
        blocking_session_id AS session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
)
SELECT
    b.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds
FROM blockers AS b
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = b.session_id
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = b.session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
ORDER BY
    at.transaction_begin_time;

Es una forma rápida de relacionar los bloqueos con la antigüedad de las transacciones.

Si el bloqueador principal está inactivo y tiene una transacción antigua, el manejo de transacciones de la aplicación se convierte en el principal sospechoso.


6. Mostrar la transacción activa más antigua de la base de datos actual

DBCC OPENTRAN es útil para una comprobación rápida a nivel de base de datos.

DBCC OPENTRAN;

También puedes apuntar a una base de datos específica:

DBCC OPENTRAN (N'YourDatabase');

Usa este comando cuando la pregunta principal sea:

¿Cuál es la transacción activa más antigua de esta base de datos?

Es especialmente útil durante las investigaciones del log de transacciones.


7. Comprobar por qué está en espera la reutilización del log de transacciones

Cuando el log está creciendo, revisa el motivo de reutilización a nivel de base de datos.

SELECT
    name AS database_name,
    recovery_model_desc,
    log_reuse_wait_desc
FROM sys.databases
WHERE
    name = DB_NAME();

Si log_reuse_wait_desc = ACTIVE_TRANSACTION, una transacción abierta está impidiendo la reutilización del log.

Luego usa las consultas de transacciones anteriores para identificar al propietario.


8. Inspeccionar el SQL reciente de un propietario de transacción inactivo

Si la sesión no tiene ninguna solicitud en curso:

DBCC INPUTBUFFER (57);

Usa el ID de sesión real.

Esto puede aportar contexto sobre la instrucción reciente de una sesión inactiva.

Tómalo como contexto, no como un historial completo de la transacción.

Qué significa el resultado

Campo Por qué importa
session_id Sesión propietaria de la transacción o que participa en ella.
session_status sleeping puede ser especialmente relevante cuando una transacción sigue abierta.
open_transaction_count Número de transacciones abiertas a nivel de sesión. Útil como señal rápida.
transaction_id Identificador interno de la transacción.
transaction_begin_time Momento en que empezó la transacción. Fundamental para analizar la antigüedad.
transaction_type Tipo de transacción registrado por SQL Server.
transaction_state Estado interno actual de la transacción.
last_request_end_time Ayuda a ver cuánto tiempo lleva inactiva una sesión en sleeping desde su última solicitud.
blocking_session_id Indica si la propia solicitud activa está bloqueada.
log_reuse_wait_desc Motivo, a nivel de base de datos, por el que el log de transacciones no puede reutilizar espacio en este momento.

Qué revisar

Transacción muy antigua

La antigüedad importa.

Una transacción antigua es más sospechosa cuando se combina con:

  • quejas de los usuarios
  • bloqueos
  • crecimiento del log
  • inactividad de la sesión
  • presión sobre el version store

No definas un único umbral universal de «demasiado antigua». Una transacción de cinco minutos puede ser normal en un sistema e inaceptable en otro.

Sesión inactiva + transacción abierta

Es uno de los hallazgos más valiosos.

Causas posibles:

  • falta de commit
  • falta de rollback
  • ruta de excepción
  • timeout del cliente
  • transacción manual
  • problema de connection pooling

ACTIVE_TRANSACTION en log_reuse_wait_desc

Esto respalda claramente una investigación de transacciones abiertas.

Encuentra la transacción más antigua y la sesión propietaria antes de actuar.

La sesión bloqueadora tiene una transacción antigua

Prioriza entender:

  • qué aplicación es la propietaria
  • qué instrucción inició o modificó la transacción
  • por qué no se ha confirmado
  • el costo del rollback si se termina la sesión

Muchas sesiones con transacciones abiertas

Esto puede indicar un patrón de la aplicación más que un problema aislado.

Busca elementos compartidos:

  • login
  • host
  • nombre del programa
  • pool de aplicaciones
  • ruta de código de manejo de transacciones

Notas de producción

open_transaction_count es útil, pero no lo dice todo

La semántica de las transacciones puede ser más compleja que un simple entero.

Usa las DMV de transacciones cuando necesites detalles fiables.

Una sesión inactiva puede seguir siendo peligrosa

Sleeping significa que no hay ninguna solicitud en ejecución en este momento.

No significa:

  • que no haya bloqueos
  • que no haya transacción
  • que no haya impacto en el log

Inspecciona siempre el estado de la transacción.

Las transacciones abiertas pueden afectar la reutilización del log

Las transacciones activas de larga duración pueden retrasar el truncamiento y la reutilización del log de transacciones.

Si el log está creciendo, correlaciona la antigüedad de las transacciones con:

sys.databases.log_reuse_wait_desc

Las transacciones abiertas pueden aumentar el costo del rollback

Cuanto más larga y más grande sea la transacción, más trabajo puede tener que deshacer SQL Server si se termina la sesión.

Antes de usar KILL, entiende:

  • la antigüedad de la transacción
  • la operación de negocio
  • el volumen de filas modificadas
  • el impacto del rollback
  • el comportamiento de las aplicaciones dependientes

Las cargas de trabajo con versionado también pueden verse afectadas

Las transacciones de larga duración pueden interactuar con la retención de versiones de fila y con la limpieza del version store.

El efecto exacto depende de las funcionalidades de aislamiento y versionado que se usen.

Los permisos varían según la versión y la plataforma

El acceso amplio a las DMV de sesiones y transacciones requiere los permisos adecuados de estado de rendimiento o de estado del servidor.

En SQL Server 2022 y versiones posteriores, Microsoft trasladó los requisitos de muchas DMV a VIEW SERVER PERFORMANCE STATE.

Las versiones anteriores suelen usar VIEW SERVER STATE.

Errores comunes

1. Suponer que las sesiones inactivas son inofensivas

Una conexión inactiva puede seguir siendo propietaria de una transacción abierta y mantener bloqueos.

2. Terminar el bloqueador antes de evaluar el riesgo del rollback

Un bloqueador con una transacción grande puede provocar un rollback largo y un mayor impacto operativo.

Primero entiende la situación.

3. Mirar solo el texto SQL actual

Es posible que la solicitud que modificó originalmente los datos ya haya terminado.

El estado de la transacción puede sobrevivir a la solicitud activa.

4. Tratar DBCC OPENTRAN como un inventario completo

Es útil para identificar la transacción activa más antigua de una base de datos.

Para todas las sesiones y transacciones, usa las DMV.

5. Ignorar los límites transaccionales de la aplicación

Los síntomas en la base de datos suelen originarse en el código de la aplicación.

Revisa:

  • el inicio de la transacción
  • el commit o rollback
  • el manejo de excepciones
  • el manejo de timeouts
  • las conexiones del pool

6. Suponer que la antigüedad de la transacción por sí sola demuestra un problema

Algunas cargas de trabajo ejecutan transacciones largas de forma legítima.

Usa la antigüedad junto con los bloqueos, la reutilización del log, la importancia de la carga de trabajo y el impacto en los recursos.

Siguiente paso

Usa el patrón de la transacción para decidir la siguiente investigación.

  • Sesión inactiva + transacción antigua → revisa el comportamiento de commit y rollback de la aplicación.
  • Una transacción abierta bloquea a otras → analiza la cadena de bloqueo e identifica el bloqueador principal.
  • log_reuse_wait_desc = ACTIVE_TRANSACTION → identifica la transacción activa más antigua y la sesión propietaria.
  • La transacción activa sigue ejecutando trabajo → revisa el SQL actual, las esperas, el CPU, las lecturas y el plan.
  • Puede que haya que terminar una transacción grande o antigua → estima el impacto operativo y el del rollback antes de usar KILL.
  • Muchas transacciones abiertas similares → investiga el manejo de transacciones en toda la aplicación.
  • No hay un problema relevante de transacciones abiertas → vuelve a un análisis más amplio de esperas, bloqueos y Query Store.

← Todas las guías rápidas