Explorar guías rápidas · SQL Server
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
- Problema
- Respuesta rápida
- Código / comandos
- 1. Listar las transacciones de usuario abiertas por antigüedad
- 2. Agregar la solicitud actual y el texto SQL
- 3. Encontrar sesiones inactivas con transacciones abiertas
- 4. Inspeccionar los detalles de transacción de una sesión
- 5. Encontrar sesiones que bloquean a otras y tienen transacciones abiertas
- 6. Mostrar la transacción activa más antigua de la base de datos actual
- 7. Comprobar por qué está en espera la reutilización del log de transacciones
- 8. Inspeccionar el SQL reciente de un propietario de transacción inactivo
- Qué significa el resultado
- Qué revisar
- Transacción muy antigua
- Sesión inactiva + transacción abierta
- ACTIVE_TRANSACTION en log_reuse_wait_desc
- La sesión bloqueadora tiene una transacción antigua
- Muchas sesiones con transacciones abiertas
- Notas de producción
- open_transaction_count es útil, pero no lo dice todo
- Una sesión inactiva puede seguir siendo peligrosa
- Las transacciones abiertas pueden afectar la reutilización del log
- Las transacciones abiertas pueden aumentar el costo del rollback
- Las cargas de trabajo con versionado también pueden verse afectadas
- Los permisos varían según la versión y la plataforma
- Errores comunes
- 1. Suponer que las sesiones inactivas son inofensivas
- 2. Terminar el bloqueador antes de evaluar el riesgo del rollback
- 3. Mirar solo el texto SQL actual
- 4. Tratar DBCC OPENTRAN como un inventario completo
- 5. Ignorar los límites transaccionales de la aplicación
- 6. Suponer que la antigüedad de la transacción por sí sola demuestra un problema
- Siguiente paso
- 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.
¿Te resultó útil este contenido?
¿Qué podríamos mejorar?
Gracias — esto me ayuda a mejorar Javi Builds.
Mantente al día
Recibe solo las novedades de Javi Builds que realmente te interesan.
Revisa tu bandeja de entrada
Te he enviado un enlace de confirmación. Tu suscripción se activará cuando la confirmes.