DBA Scripts: Get Open Transactions

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
*/
-- 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

Run on the lab instance. 5 rows come back, and none of them are mine: every one is a SQL Agent collector job. Sessions 51 and 66 had been holding a read/write transaction open for 93 and 83 minutes, both still Active rather than rolling back.

That is worth noticing, because it was scheduled work rather than somebody who walked away from a query window. A job that opens a transaction and holds it for its whole run looks identical to a forgotten transaction from the outside, and it does the same damage to log truncation.

A session appears once per database its transaction has touched, which is why 2 sessions produce 5 rows: session 66 shows against DBAMonitor, tempdb and a NULL. That NULL is the transaction existing without a database context, not a bug.

If it looks like it is hanging, compare cpu_time against total_elapsed_time before you blame the query. Near-zero CPU against a long elapsed time means it never started, and it is waiting for a memory grant rather than working.

Understanding the Results

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

Microsoft’s reference covers sys.dm_exec_sessions, sys.dm_exec_requests and sys.dm_exec_sql_text in full.

Related Scripts

You may also find these scripts useful:

• Blocking and Locking (hub)
• Blocking Chains
• Blocking Chains with Plan / Contention Analysis / Lock Escalation Stats
• Blocking Sessions
• Deadlock Summary
• Log Reuse Waits
• Troubleshoot SQL Server Blocking, Step by Step
• Troubleshoot RESOURCE_SEMAPHORE Waits
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

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.