Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: TroubleshootingDifficulty: IntermediateUpdated

Find Open Transactions

Identify open transactions, find sessions holding them, measure transaction age, and connect long-running transactions to blocking, log growth, and application behavior.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. List open user transactions by age
  5. 2. Add current request and SQL text
  6. 3. Find sleeping sessions with open transactions
  7. 4. Inspect one session’s transaction details
  8. 5. Find sessions that are blocking others and have open transactions
  9. 6. Show the oldest active transaction in the current database
  10. 7. Check why transaction log reuse is waiting
  11. 8. Inspect recent SQL for a sleeping transaction owner
  12. What the result means
  13. What to look for
  14. Very old transaction
  15. Sleeping session + open transaction
  16. ACTIVE_TRANSACTION in log_reuse_wait_desc
  17. Blocking session owns old transaction
  18. Many sessions with open transactions
  19. Production notes
  20. open_transaction_count is useful but not the whole story
  21. A sleeping session can still be dangerous
  22. Open transactions can affect log reuse
  23. Open transactions can increase rollback cost
  24. Versioned workloads may also be affected
  25. Permissions vary by version and platform
  26. Common mistakes
  27. 1. Assuming sleeping sessions are harmless
  28. 2. Killing the blocker before checking rollback risk
  29. 3. Looking only at current SQL text
  30. 4. Treating DBCC OPENTRAN as a full inventory
  31. 5. Ignoring application transaction boundaries
  32. 6. Assuming transaction age alone proves a problem
  33. Next step
  34. Related deep dives

Problem

SQL Server is experiencing blocking, the transaction log is not truncating as expected, a session appears idle but still holds locks, or you suspect an application left a transaction open.

The goal is to answer:

  • Which sessions currently have open transactions?
  • How old are those transactions?
  • Is the owning session active or sleeping?
  • What was the session doing?
  • Is an open transaction blocking other sessions?
  • Could an old transaction be contributing to transaction-log growth?

Quick answer

Start by joining session, transaction, and active-transaction DMVs.

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    at.transaction_id,
    at.name AS transaction_name,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.is_user_process = 1
ORDER BY
    at.transaction_begin_time;

Focus first on the oldest transactions, especially when the owning session is sleeping or blocking other work.

Code / commands

1. List open user transactions by age

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.is_user_process = 1
ORDER BY
    at.transaction_begin_time;

This is the best first-pass inventory.

Older transactions deserve attention because they can:

  • retain locks
  • delay version cleanup
  • contribute to log-reuse problems
  • indicate poor application transaction handling

2. Add current request and SQL text

Use this when you want to know whether the transaction owner is actively executing work.

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,

    r.status AS request_status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS wait_ms,

    txt.text AS batch_text
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    s.is_user_process = 1
ORDER BY
    at.transaction_begin_time;

Important: a session can own an open transaction even when it has no active request.

That is why sys.dm_exec_requests is left joined.


3. Find sleeping sessions with open transactions

These are especially important during blocking incidents.

SELECT
    s.session_id,
    s.status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,
    s.last_request_start_time,
    s.last_request_end_time,

    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.is_user_process = 1
    AND s.status = N'sleeping'
ORDER BY
    at.transaction_begin_time;

A sleeping session with an old transaction can still hold locks.

That often points to:

  • application code that did not commit/rollback
  • connection pooling with unfinished transaction state
  • manual transactions left open
  • error handling that skipped cleanup

4. Inspect one session’s transaction details

DECLARE @session_id int = 57;

SELECT
    s.session_id,
    s.status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    st.transaction_id,
    st.is_user_transaction,
    st.is_local,

    at.name AS transaction_name,
    at.transaction_begin_time,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.session_id = @session_id;

Use this after another diagnostic identifies a suspicious SPID.


5. Find sessions that are blocking others and have open transactions

WITH blockers AS
(
    SELECT DISTINCT
        blocking_session_id AS session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
)
SELECT
    b.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,

    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds
FROM blockers AS b
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = b.session_id
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = b.session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
ORDER BY
    at.transaction_begin_time;

This is a fast way to connect blocking with transaction age.

If the head blocker is sleeping and has an old transaction, application transaction management becomes a prime suspect.


6. Show the oldest active transaction in the current database

DBCC OPENTRAN is useful for a quick database-level check.

DBCC OPENTRAN;

You can also target a specific database:

DBCC OPENTRAN (N'YourDatabase');

Use this when the main question is:

What is the oldest active transaction in this database?

It is especially useful during transaction-log investigations.


7. Check why transaction log reuse is waiting

When the log is growing, inspect the database-level reuse reason.

SELECT
    name AS database_name,
    recovery_model_desc,
    log_reuse_wait_desc
FROM sys.databases
WHERE
    name = DB_NAME();

If log_reuse_wait_desc = ACTIVE_TRANSACTION, an open transaction is preventing log reuse.

Then use the transaction queries above to identify the owner.


8. Inspect recent SQL for a sleeping transaction owner

If the session has no current request:

DBCC INPUTBUFFER (57);

Use the actual session ID.

This can provide recent statement context for a sleeping session.

Treat it as context, not a complete transaction history.

What the result means

Field Why it matters
session_id Session that owns or participates in the transaction.
session_status sleeping can be especially important when a transaction remains open.
open_transaction_count Session-level count of open transactions. Useful as a quick signal.
transaction_id Internal identifier for the transaction.
transaction_begin_time When the transaction started. Critical for age analysis.
transaction_type Type of transaction recorded by SQL Server.
transaction_state Current internal transaction state.
last_request_end_time Helps show how long a sleeping session has been idle since its last request.
blocking_session_id Shows whether the active request is itself blocked.
log_reuse_wait_desc Database-level reason the transaction log cannot currently reuse space.

What to look for

Very old transaction

Age matters.

An old transaction is more suspicious when combined with:

  • user complaints
  • blocking
  • growing log
  • session inactivity
  • version-store pressure

Do not define one universal “too old” threshold. A five-minute transaction may be normal in one system and unacceptable in another.

Sleeping session + open transaction

This is one of the highest-value findings.

Possible causes:

  • missing commit
  • missing rollback
  • exception path
  • client timeout
  • manual transaction
  • connection pooling issue

ACTIVE_TRANSACTION in log_reuse_wait_desc

This strongly supports an open-transaction investigation.

Find the oldest transaction and the owning session before taking action.

Blocking session owns old transaction

Prioritize understanding:

  • what application owns it
  • what statement started or modified the transaction
  • why it has not committed
  • rollback cost if terminated

Many sessions with open transactions

This may indicate an application pattern rather than a one-off problem.

Look for shared:

  • login
  • host
  • program name
  • application pool
  • transaction-management code path

Production notes

open_transaction_count is useful but not the whole story

Transaction semantics can be more complex than one integer.

Use transaction DMVs when you need authoritative details.

A sleeping session can still be dangerous

Sleeping means no request is currently running.

It does not mean:

  • no locks
  • no transaction
  • no log impact

Always inspect transaction state.

Open transactions can affect log reuse

Long-running active transactions can delay transaction-log truncation/reuse.

If the log is growing, correlate transaction age with:

sys.databases.log_reuse_wait_desc

Open transactions can increase rollback cost

The longer and larger the transaction, the more work SQL Server may need to undo if the session is terminated.

Before using KILL, understand:

  • transaction age
  • business operation
  • row-change volume
  • rollback impact
  • downstream application behavior

Versioned workloads may also be affected

Long-running transactions can interact with row-version retention and version-store cleanup behavior.

The exact effect depends on isolation/versioning features in use.

Permissions vary by version and platform

Broad access to session/transaction DMVs requires appropriate performance/server-state permissions.

For SQL Server 2022+, Microsoft shifted many DMV requirements to VIEW SERVER PERFORMANCE STATE.

Older versions commonly use VIEW SERVER STATE.

Common mistakes

1. Assuming sleeping sessions are harmless

A sleeping connection can still own an open transaction and hold locks.

2. Killing the blocker before checking rollback risk

A blocker with a large transaction can create a long rollback and more operational impact.

Understand first.

3. Looking only at current SQL text

The request that originally modified data may already be finished.

Transaction state can outlive the active request.

4. Treating DBCC OPENTRAN as a full inventory

It is useful for identifying the oldest active transaction in a database.

For all sessions/transactions, use the DMVs.

5. Ignoring application transaction boundaries

Database-side symptoms often originate in application code.

Check:

  • transaction begin
  • commit/rollback
  • exception handling
  • timeout handling
  • pooled connections

6. Assuming transaction age alone proves a problem

Some workloads legitimately run long transactions.

Use age together with blocking, log reuse, workload importance, and resource impact.

Next step

Use the transaction pattern to decide the next investigation.

  • Sleeping session + old transaction → inspect application commit/rollback behavior.
  • Open transaction blocks others → analyze the blocking chain and identify the head blocker.
  • log_reuse_wait_desc = ACTIVE_TRANSACTION → identify the oldest active transaction and owning session.
  • Active transaction is still running work → inspect current SQL, waits, CPU, reads, and plan.
  • Large/old transaction may need termination → estimate operational and rollback impact before using KILL.
  • Many similar open transactions → investigate application-wide transaction handling.
  • No meaningful open transaction issue → return to broader wait, blocking, and Query Store analysis.

← All cheat sheets