Browse cheat sheets · SQL Server
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
- Problem
- Quick answer
- Code / commands
- 1. List open user transactions by age
- 2. Add current request and SQL text
- 3. Find sleeping sessions with open transactions
- 4. Inspect one session’s transaction details
- 5. Find sessions that are blocking others and have open transactions
- 6. Show the oldest active transaction in the current database
- 7. Check why transaction log reuse is waiting
- 8. Inspect recent SQL for a sleeping transaction owner
- What the result means
- What to look for
- Very old transaction
- Sleeping session + open transaction
- ACTIVE_TRANSACTION in log_reuse_wait_desc
- Blocking session owns old transaction
- Many sessions with open transactions
- Production notes
- open_transaction_count is useful but not the whole story
- A sleeping session can still be dangerous
- Open transactions can affect log reuse
- Open transactions can increase rollback cost
- Versioned workloads may also be affected
- Permissions vary by version and platform
- Common mistakes
- 1. Assuming sleeping sessions are harmless
- 2. Killing the blocker before checking rollback risk
- 3. Looking only at current SQL text
- 4. Treating DBCC OPENTRAN as a full inventory
- 5. Ignoring application transaction boundaries
- 6. Assuming transaction age alone proves a problem
- Next step
- 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.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.