Otvorené transakcie – kto ich drží a ako dlho

/* DeadlockCafe | Otvorené transakcie – kto ich drží a ako dlho
   Účel:  Session s otvorenou transakciou: vek transakcie, typ, stav, koľko logu zaberá a posledný príkaz. Na rastúci log a „zabudnutý“ COMMIT.
   Režim: READ ONLY | SQL Server 2016 – 2022, on-premises
*/
SET NOCOUNT ON;

SELECT st.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(MINUTE, at.transaction_begin_time, SYSDATETIME()) AS tran_age_minutes,
       CASE at.transaction_type WHEN 1 THEN 'read/write' WHEN 2 THEN 'read-only'
            WHEN 3 THEN 'system' WHEN 4 THEN 'distributed' END      AS transaction_type,
       CASE at.transaction_state WHEN 0 THEN 'not initialized' WHEN 1 THEN 'initialized, not started'
            WHEN 2 THEN 'active' WHEN 3 THEN 'ended (read-only)' WHEN 4 THEN 'commit initiated (DTC)'
            WHEN 5 THEN 'prepared, waiting' WHEN 6 THEN 'committed' WHEN 7 THEN 'rolling back'
            WHEN 8 THEN 'rolled back' END                          AS transaction_state,
       CAST(dt.log_bytes_used / 1048576.0 AS decimal(18,1))        AS log_used_mb,
       dt.database_names,
       ib.event_info            AS last_submitted_command
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_tran_active_transactions AS at ON at.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id
OUTER APPLY (SELECT SUM(d.database_transaction_log_bytes_used) AS log_bytes_used,
                    STUFF((SELECT N', ' + DB_NAME(d2.database_id)
                           FROM sys.dm_tran_database_transactions AS d2
                           WHERE d2.transaction_id = st.transaction_id
                           FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, N'') AS database_names
             FROM sys.dm_tran_database_transactions AS d
             WHERE d.transaction_id = st.transaction_id) AS dt
OUTER APPLY sys.dm_exec_input_buffer(st.session_id, NULL) AS ib
WHERE s.is_user_process = 1
ORDER BY at.transaction_begin_time;