Find long open transactions

A session leads to locks or the log grows while a transaction remains open. We need to measure how long it exists and relate it to the session that maintains it.

What we need to distinguish

A session is the logical connection of the client; a request is the job you are currently running and a transaction groups changes pending to be committed or rolled back. The request may terminate while the session keeps the transaction open, so we need to look at the three layers separately.

We will also distinguish the age of the transaction from the current state of the session. An old transaction deserves context, but its duration alone does not prove it to be incorrect.

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
DECLARE @MinutosAbierta INT = 5;

SELECT
    st.session_id AS IdSesion,
    ta.transaction_id AS IdTransaccion,
    ta.transaction_begin_time AS InicioTransaccion,
    DATEDIFF_BIG
    (
        SECOND,
        ta.transaction_begin_time,
        SYSDATETIME()
    ) AS SegundosAbierta,
    ta.name AS NombreTransaccion,
    CASE ta.transaction_type
        WHEN 1 THEN N'Lectura y escritura'
        WHEN 2 THEN N'Solo lectura'
        WHEN 3 THEN N'Sistema'
        WHEN 4 THEN N'Distribuida'
        ELSE N'Otro'
    END AS TipoTransaccion,
    ta.transaction_state AS EstadoTransaccion,
    st.open_transaction_count AS TransaccionesAbiertasSesion,
    s.status AS EstadoSesion,
    s.login_name AS InicioSesion,
    s.host_name AS EquipoCliente,
    s.program_name AS Aplicacion,
    solicitud.status AS EstadoSolicitud,
    solicitud.command AS Comando,
    solicitud.wait_type AS TipoEspera,
    texto.text AS TextoActualOReciente
FROM sys.dm_tran_active_transactions AS ta
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.transaction_id = ta.transaction_id
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = st.session_id
OUTER APPLY
(
    SELECT TOP (1)
        r.status,
        r.command,
        r.wait_type,
        r.sql_handle,
        r.start_time
    FROM sys.dm_exec_requests AS r
    WHERE r.session_id = st.session_id
    ORDER BY r.start_time DESC, r.request_id
) AS solicitud
OUTER APPLY
(
    SELECT TOP (1)
        c.most_recent_sql_handle
    FROM sys.dm_exec_connections AS c
    WHERE c.session_id = st.session_id
    ORDER BY c.connect_time DESC
) AS conexion
OUTER APPLY sys.dm_exec_sql_text
(
    COALESCE(solicitud.sql_handle, conexion.most_recent_sql_handle)
) AS texto
WHERE st.is_user_transaction = 1
  AND s.is_user_process = 1
  AND ta.transaction_begin_time <=
      DATEADD(MINUTE, -@MinutosAbierta, SYSDATETIME())
ORDER BY
    ta.transaction_begin_time,
    st.session_id;
GO

Parameters or permissions

Adjust @MinutosAbierta to the normal behavior of the system. A five-minute transaction can be abnormal in a transactional system and expected during a batch process. Dynamic management views (DMVs) expose the changing state of transactions and require server health or performance permissions. A distributed transaction or bound session can relate to more than one session; Multiple Active Result Sets (MARS) can also produce multiple transactions for the same session.

How to read the result

HomeTransaction is the most important mark to measure seniority. SessionState and RequestState do not mean the same thing: the transaction can remain open even if there is no request running. CurrentOrRecentText uses the active request when it exists and, otherwise, the connection’s most recent SQL. This text guides the search in code and traces, but does not necessarily identify the exact instruction that opened the transaction.

What this does establish

It allows you to locate user transactions that exceed a time threshold and relate them to sessions, applications, and available text.

What this still does not establish

It does not calculate the cost of rolling them back, it does not list all of its locks, and it does not determine whether its duration is correct for the process. It also doesn’t justify running KILL.

Next reasonable check

If the session is idle, checks to see if it keeps a transaction. If it is waiting, check the blocking sessions and active request waits. Before intervening, identify the proprietary process and estimate the consequences of confirming or reversing its work.

Search SQL Server