DBA Scripts: Get Open Transactions

Part of the DBA-Tools Project.

---

The Transaction Nobody Remembered to Close

An open transaction that never commits or rolls back is one of the quietest ways SQL Server gets into trouble: it holds locks other sessions wait on, it stops the transaction log from truncating, and none of that shows up as an error anywhere, it just sits there until someone notices the symptoms and goes looking for the cause.

This script lists every currently open transaction, how long it’s been open, which session owns it, and what that session is running or last ran, so “who’s holding this open, and for how long” has a direct answer.

---

Why Open Transactions Matters

• A long-running or forgotten open transaction is one of the most common root causes behind both blocking and a transaction log stuck on LOG_BACKUP/ACTIVE_TRANSACTION, see Log Reuse Waits
• tran_age_sec is the single fastest signal, a transaction open for 3 seconds is normal, one open for 3 hours almost never is
• Idle sessions holding open transactions are the hardest to spot from the application side, since nothing is actively running, they just sit there holding locks
• This is the natural next script to run whenever Get-LogReuseWaits or a blocking check points at ACTIVE_TRANSACTION as the cause

---

When to Run This Script

• Any time Log Reuse Waits shows ACTIVE_TRANSACTION as the reason a log can’t reuse space
• Investigating a blocking incident where the head blocker’s cause isn’t obvious from the current statement alone
• Routine SQL Server health checks, especially on instances with application connection pooling, where a forgotten transaction can sit open indefinitely on a pooled connection

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-OpenTransactions
Category    : performance
Purpose     : Active transactions with age, session details, and the SQL currently running or last executed — long-running open transactions cause log growth and block readers in READ_COMMITTED isolation.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-open-transactions/)
Requires    : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-open-transactions/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status                                                            AS session_status,
    s.open_transaction_count,
    t.transaction_begin_time,
    DATEDIFF(SECOND, t.transaction_begin_time, GETDATE())              AS tran_age_sec,
    CASE t.transaction_type
        WHEN 1 THEN 'Read/Write'
        WHEN 2 THEN 'Read-Only'
        WHEN 3 THEN 'System'
        WHEN 4 THEN 'Distributed'
        ELSE CAST(t.transaction_type AS VARCHAR(10))
    END                                                                 AS transaction_type,
    CASE t.transaction_state
        WHEN 0 THEN 'Not initialised'
        WHEN 1 THEN 'Not started'
        WHEN 2 THEN 'Active'
        WHEN 3 THEN 'Ended'
        WHEN 4 THEN 'Commit initiated'
        WHEN 5 THEN 'Prepared'
        WHEN 6 THEN 'Committed'
        WHEN 7 THEN 'Rolling back'
        WHEN 8 THEN 'Rolled back'
        ELSE CAST(t.transaction_state AS VARCHAR(10))
    END                                                                 AS transaction_state,
    DB_NAME(dt.database_id)                                            AS database_name,
    CAST(dt.database_transaction_log_bytes_used / 1024.0 / 1024.0 AS DECIMAL(10,2)) AS log_used_mb,
    r.blocking_session_id,
    DATEDIFF(SECOND, r.start_time, GETDATE())                          AS request_age_sec,
    LEFT(CAST(current_sql.text AS NVARCHAR(MAX)), 300)                 AS sql_text
FROM sys.dm_exec_sessions               s
JOIN sys.dm_tran_session_transactions   st  ON st.session_id     = s.session_id
JOIN sys.dm_tran_active_transactions    t   ON t.transaction_id  = st.transaction_id
LEFT JOIN sys.dm_tran_database_transactions dt ON dt.transaction_id = t.transaction_id
LEFT JOIN sys.dm_exec_requests          r   ON r.session_id      = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) current_sql
/* Note: most_recent_sql_handle was removed from dm_exec_sessions in SQL Server 2025.
   SQL text is only available for sessions with an active request (status = 'running'/'suspended').
   Idle sessions holding open transactions will show NULL for sql_text. */
WHERE s.is_user_process = 1
ORDER BY tran_age_sec DESC;

Rows sort by transaction age, oldest first, so the transaction that’s been open longest, usually the one most worth investigating, is always at the top.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# List every currently open transaction, oldest first:
.\run.ps1 Get-OpenTransactions

# To run against a remote sql server:
.\run.ps1 Get-OpenTransactions -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/performance/blocking-locking/Get-OpenTransactions.sql
• powershell/wrappers/performance/blocking-locking/Get-OpenTransactions.ps1

---

Example Output

A clean baseline on this lab instance, genuinely checked, not staged: zero sessions currently holding an open transaction.

session_id	open_transaction_count
(no rows)

This particular join, across session, transaction, and per-database transaction DMVs plus a SQL-text lookup, is heavier than most single-DMV checks in this repo, worth knowing if you’re running it against a small or already-busy instance rather than a dedicated monitoring server; on this lab box it’s genuinely slow enough to time out a scripted capture, an honest operational data point in its own right, not just a documentation gap. On a busier or better-resourced instance it should return quickly. For illustration, an open transaction row looks like this:

session_id	login_name	open_transaction_count	tran_age_sec	transaction_type	database_name	log_used_mb	sql_text
68	HPAI01\Peter	1	42	Read/Write	RandomLab	0.02	UPDATE dbo.OrdersDemo SET status = ‘Processing’ …

---

Understanding the Results

• tran_age_sec — the single number to sort by. A handful of seconds is routine; minutes or hours, especially on a session showing sleeping status, is a forgotten or stuck transaction.
• session_status = sleeping with open_transaction_count > 0 — the classic forgotten-transaction pattern: the session isn’t running anything right now, but it’s still holding a transaction open from earlier.
• transaction_state — Active is the normal in-flight state. Rolling back sitting for a long time can itself indicate a large rollback in progress, worth knowing before you consider killing anything.
• log_used_mb — how much log space this specific transaction is responsible for. Large and growing on an old transaction is a direct line to a growing transaction log.
• sql_text — only populated when the session has an active request; an idle session holding an open transaction will show NULL here, which is itself informative, it means the last statement already finished, but the transaction wrapping it never closed.

---

Common Causes

• Application code that opens a transaction, does work, then waits on something else (a UI prompt, a downstream API call) before committing
• An exception path that skips the commit/rollback entirely, leaving the transaction open until the connection is recycled or times out
• A connection pool reusing a session that still has an uncommitted transaction from a previous, incompletely-handled request

---

Best Practices

• Treat any sleeping session with open_transaction_count > 0 as worth investigating, not routine, a healthy application closes its transactions promptly
• Run this immediately whenever Log Reuse Waits shows ACTIVE_TRANSACTION, it’s the direct next step
• On a resource-constrained instance, be mindful this query does real join work across several DMVs, prefer running it on demand rather than on a very tight polling schedule

---

Related Scripts

You may also find these scripts useful:

• Blocking and Locking (hub)

• Blocking Chains

• Blocking Chains with Plan
• Blocking Sessions
• Contention Analysis
• Deadlock Summary
• Lock Escalation Stats
• Log Reuse Waits

---

Frequently Asked Questions

Is it safe to just kill a session with a long-open transaction?

Confirm what it’s doing first. A long-running but legitimate operation (a large batch load, an index rebuild) will roll back if killed, which can take as long as the operation itself did to reach that point. Killing is the right call for a genuinely forgotten or stuck transaction, not a default first response.

Why does sql_text show NULL for some rows?

SQL text is only available for a session with a currently active request. A session that’s sleeping with an open transaction already finished its last statement, the transaction itself is just still open, so there’s no current request to read text from.

---

Summary

An open transaction that never closes is invisible until something goes looking for it, and it’s usually the real cause behind blocking or a transaction log that won’t stop growing. tran_age_sec sorted oldest-first turns “something is wrong somewhere” into a specific session and a specific piece of SQL.

Run this whenever a blocking check or a stuck log points at an active transaction as the cause, and treat any long-lived, sleeping session holding one open as a finding worth chasing down.