SQL Server에서 장시간 열려 있는 트랜잭션을 확인하는 SQL

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: VIEW SERVER STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    at.transaction_id,
    at.name                                                AS transaction_name,
    at.transaction_begin_time,
    DATEDIFF(SECOND, at.transaction_begin_time, GETDATE()) AS open_seconds,
    at.transaction_type,        -- 1:読み書き 2:読み取り専用 3:システム 4:分散
    at.transaction_state,       -- 2:アクティブ 3:終了(読み取り専用) 7:ロールバック中
    st.session_id,
    st.is_user_transaction,
    st.open_transaction_count,
    es.login_name,
    es.host_name,
    es.program_name,
    es.status                                              AS session_status,
    es.last_request_start_time,
    es.last_request_end_time,
    ec.client_net_address,
    ec.connect_time,
    txt.text                                               AS last_statement
FROM sys.dm_tran_active_transactions AS at
INNER JOIN sys.dm_tran_session_transactions AS st
        ON at.transaction_id = st.transaction_id
LEFT JOIN sys.dm_exec_sessions AS es
       ON st.session_id = es.session_id
LEFT JOIN sys.dm_exec_connections AS ec
       ON st.session_id = ec.session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS txt
WHERE st.is_user_transaction = 1
ORDER BY at.transaction_begin_time ASC;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: VIEW SERVER STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT TOP (1)
    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(SECOND, at.transaction_begin_time, GETDATE()) AS open_seconds,
    st.session_id,
    st.open_transaction_count,
    es.login_name,
    es.host_name,
    es.program_name,
    es.status                                              AS session_status
FROM sys.dm_tran_active_transactions AS at
INNER JOIN sys.dm_tran_session_transactions AS st
        ON at.transaction_id = st.transaction_id
LEFT JOIN sys.dm_exec_sessions AS es
       ON st.session_id = es.session_id
WHERE st.is_user_transaction = 1
ORDER BY at.transaction_begin_time ASC;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: VIEW SERVER STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    r.session_id,
    r.status,
    r.command,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.cpu_time,
    r.total_elapsed_time,
    r.open_transaction_count,
    r.percent_complete,          -- ROLLBACK など一部の操作で進捗率が入る
    SUBSTRING(
        t.text,
        (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset
               WHEN -1 THEN DATALENGTH(t.text)
               ELSE r.statement_end_offset
          END - r.statement_start_offset) / 2) + 1
    )                            AS running_statement
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: sysadmin または db_owner
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
USE [SampleDB];
GO
DBCC OPENTRAN;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: ALTER ANY CONNECTION / processadmin / sysadmin
-- 変更作業: あり（セッション強制終了 → 未コミットトランザクションのロールバック）
-- Production 実行: 原則不可。業務影響の確認と承認を得てから実行すること
-- 57 は前掲のクエリで特定した session_id に置き換える
KILL 57;

-- 既にロールバック中のセッションについて進捗率を確認する
KILL 57 WITH STATUSONLY;