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

Applicability of This Document

Ready-to-Run Commands

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

How to Read the Results

When to Use This

• 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

Possible Causes (Most Likely First)

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.

Diagnostic Steps

1. 1

   List open transactions

   Read-only

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

2. 2

   Identify the oldest transaction

   Read-only

   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

   Read-only

   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

   Read-only

   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

   Read-only

   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

   Read-only

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

Solutions

Differences by Version and Environment

If This Doesn't Resolve It

• 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.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

• 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

What the Risk Labels Mean

• Read-onlyDoes not change data or settings.
• LowImpact is limited, but permissions and load should be checked.
• MediumMay affect performance, locking, or cost.
• HighMay cause an outage, data loss, or require recovery work.
• Expert Review RequiredRequires a separate review before applying to production.

GIIP's Scope of Support

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.

Related Services

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