SQL to Check for Long-Running Open Transactions in SQL Server

この文書の適用条件

そのまま実行できるコマンド

-- 対象: 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;

結果の読み方

こういう状況で使います

• The transaction log is not being released, and `log_reuse_wait_desc` stays fixed at ACTIVE_TRANSACTION
• Updates to a specific table are blocked for long periods, causing frequent timeouts
• Restarting the application temporarily resolves it, but it recurs after a while
• Lock-waiting sessions pile up and the connection count approaches its limit

考えられる原因（可能性の高い順）

1. 01

   The application does not commit or roll back after BEGIN TRAN

   If exception handling has a path where neither commit nor rollback is reached, the transaction remains open even after the connection returns to the pool. This shows up as `session_status` being sleeping while a transaction is still open.

2. 02

   A transaction was left open from an interactive tool

   This happens when someone runs `BEGIN TRAN` in a management tool and walks away without closing the window. You can identify this by `program_name`.

3. 03

   A rollback of a large update is in progress

   If `transaction_state = 7`, it is already rolling back. There is no way to shorten this state other than waiting — repeated `KILL`s will not speed it up.

4. 04

   An unresolved distributed transaction remains open

   A distributed transaction (`transaction_type = 4`) depends on the state of the coordinator (MS DTC). Database-side operations alone may not resolve it.

5. 05

   Application-side timeout and DB-side wait are out of sync

   Even if the client times out and abandons the operation, the server-side transaction does not end automatically — it remains until the connection is closed.

確認手順

1. 1

   List open transactions

   参照のみ

   Run the first SQL and check entries with the largest `open_seconds` first.

2. 2

   Identify the oldest transaction

   参照のみ

   If log release has stopped, the single oldest transaction is the cause. Narrow it down with the second SQL.

3. 3

   Determine whether it is still processing or abandoned

   参照のみ

   Check whether `sys.dm_exec_requests` has a row for that session. No row means processing has finished and only the transaction remains.

4. 4

   Trace the blocking chain

   参照のみ

   Follow `blocking_session_id` to identify the session at the root of the chain. Stopping anything other than the root will not resolve it.

5. 5

   Identify the originating application

   参照のみ

   Use `program_name`, `host_name`, and `client_net_address` to determine which application and server the connection is coming from.

6. 6

   Check whether it recurs at the same time of day

   参照のみ

   Cross-reference against execution history to see whether it coincides with a scheduled batch.

対応方法

バージョン・環境による違い

これで解決しない場合に確認すること

• Breakdown of lock waits

  Check `sys.dm_tran_locks` to see which resource has which lock mode held against it.

• The application's connection pool settings

  Check whether the transaction is reset when a connection returns to the pool, and the pool's max size and wait timeout.

• Execution history of batch jobs

  Check `msdb.dbo.sysjobhistory` for any job running at the same time.

• Isolation level settings

  Check the application-side configuration for whether a higher-than-default isolation level is being held for a long time.

この文書の根拠と限界

よくある質問

この文書がカバーする質問

• Detecting abandoned BEGIN TRAN sessions
• I want to find uncommitted transactions in SQL Server
• I want to identify the transaction preventing the log from being released
• I want to find the originating session of a blocking chain

リスク表示の意味

• 参照のみデータと設定を変更しません。
• 低影響は限定的ですが、権限と負荷の確認が必要です。
• 中性能・ロック・コストに影響する可能性があります。
• 高障害・データ損失・復旧作業が発生する可能性があります。
• 専門家レビュー必須本番適用前に別途レビューが必須です。

GIIPの対応範囲

An abandoned transaction has the property that no one notices it the moment it happens — by the time it surfaces as log exhaustion or blocking, the business process has already stopped. At GIIP, we periodically pull transaction open-duration data across multiple databases, and once a threshold is crossed, an AI agent identifies the responsible session and originating application before notifying the person in charge. High-impact operations like `KILL` are never run automatically — judgment and approval remain a human responsibility.

関連サービス

• 24時間監視の対象を確認する
• 障害対応・火消し支援