Age Open Transactions with sys.dm_tran_active_transactions

calendar Aug 2, 2026 / · 10 min read · sql server sys.dm_tran_active_transactions sys.dm_tran_session_transactions sys.dm_exec_sessions long running transactions transaction log blocking monitoring dmv scripts  ·
Share on: linkedin copy

A transaction that never commits keeps every log record written after it pinned in place, no matter how much work piles up behind it. sys.dm_tran_active_transactions lists every transaction currently open on the instance, but on its own it only shows a transaction ID and a start time — pairing it with sys.dm_tran_session_transactions and sys.dm_exec_sessions turns that anonymous row into a specific session, login, and elapsed duration worth investigating.

Purpose and Overview

The transaction log can only truncate up to the oldest point still needed for recovery, and an open transaction — no matter how small the actual data change behind it — holds that point fixed wherever it began. A transaction log backup running on schedule and a CHECKPOINT completing normally free nothing if a session opened a transaction ten minutes ago and hasn't committed or rolled back since. The log keeps growing, autogrow events start firing, and the eventual full-log error traces back to a single session that most routine monitoring never surfaces directly.

sys.dm_tran_active_transactions is the server-scoped DMV that lists every transaction currently active on the instance, but by itself it's anonymous — it exposes a transaction_id, a transaction_begin_time, and a transaction_type, with no session, login, or application attached. Joining it to sys.dm_tran_session_transactions supplies the missing session_id, and a further join to sys.dm_exec_sessions resolves that session_id into a login name, host name, and client application. The three-way join is what turns "there's an open transaction somewhere" into "this login, from this application, has had a transaction open for eleven minutes."

The script below produces two views of that same join: a detail query that ages every currently open read/write and system transaction and flags the ones past a 300-second threshold, and a summary rollup that groups by the session's current database to show, at a glance, which database has the oldest open read/write transaction and how many are currently outstanding.

Code Breakdown

The detail query joins all three DMVs, decodes the numeric transaction_type and transaction_state columns into readable text, and computes each transaction's age in seconds.

 1SELECT
 2    at.transaction_id,
 3    at.name                                                        AS transaction_name,
 4    at.transaction_begin_time,
 5    DATEDIFF(SECOND, at.transaction_begin_time, SYSDATETIME())      AS duration_seconds,
 6    CASE at.transaction_type
 7        WHEN 1 THEN 'Read/write'
 8        WHEN 2 THEN 'Read-only'
 9        WHEN 3 THEN 'System'
10        WHEN 4 THEN 'Distributed'
11        ELSE 'Unknown'
12    END                                                             AS transaction_type_desc,
13    CASE at.transaction_state
14        WHEN 0 THEN 'Not initialized'
15        WHEN 1 THEN 'Initialized, not started'
16        WHEN 2 THEN 'Active'
17        WHEN 3 THEN 'Ended (read-only)'
18        WHEN 4 THEN 'Commit initiated (distributed)'
19        WHEN 5 THEN 'Prepared, awaiting resolution'
20        WHEN 6 THEN 'Committed'
21        WHEN 7 THEN 'Rolling back'
22        WHEN 8 THEN 'Rolled back'
23        ELSE 'Unknown'
24    END                                                             AS transaction_state_desc,
25    st.session_id,
26    st.is_user_transaction,
27    st.is_local,
28    es.login_name,
29    es.host_name,
30    es.program_name,
31    es.status                                                      AS session_status,
32    es.login_time,
33    DB_NAME(es.database_id)                                        AS database_name
34FROM sys.dm_tran_active_transactions AS at
35INNER JOIN sys.dm_tran_session_transactions AS st
36    ON at.transaction_id = st.transaction_id
37INNER JOIN sys.dm_exec_sessions AS es
38    ON st.session_id = es.session_id
39WHERE at.transaction_type IN (1, 3)
40    AND DATEDIFF(SECOND, at.transaction_begin_time, SYSDATETIME()) > 300
41ORDER BY at.transaction_begin_time ASC;

A companion summary rolls the same join up to the database level, using a declared variable for the age threshold instead of a hard-coded literal:

 1DECLARE @ThresholdMinutes INT = 5;
 2
 3SELECT
 4    DB_NAME(es.database_id)                                            AS database_name,
 5    COUNT(DISTINCT at.transaction_id)                                  AS open_transaction_count,
 6    MIN(at.transaction_begin_time)                                     AS oldest_transaction_start,
 7    MAX(DATEDIFF(SECOND, at.transaction_begin_time, SYSDATETIME()))    AS longest_duration_seconds
 8FROM sys.dm_tran_active_transactions AS at
 9INNER JOIN sys.dm_tran_session_transactions AS st
10    ON at.transaction_id = st.transaction_id
11INNER JOIN sys.dm_exec_sessions AS es
12    ON st.session_id = es.session_id
13WHERE at.transaction_type = 1
14    AND DATEDIFF(SECOND, at.transaction_begin_time, SYSDATETIME()) >= @ThresholdMinutes * 60
15GROUP BY DB_NAME(es.database_id)
16ORDER BY longest_duration_seconds DESC;

Joining active_transactions to session_transactions and sessions

sys.dm_tran_active_transactions carries no session_id column at all. Microsoft documents that through bound sessions and distributed transactions a transaction can run under more than one session, so the session link lives in a separate view. sys.dm_tran_session_transactions is the bridge: it holds a row correlating each session to its transaction (one row per session when a transaction spans several), and joining on transaction_id supplies the session_id needed to reach sys.dm_exec_sessions. The final join on session_id resolves login_name, host_name, and program_name — the detail that turns a bare transaction_id into an actionable "which application, which login" answer.

Decoding transaction_type and transaction_state

Both columns arrive from the DMV as plain integers, and neither is self-explanatory without the CASE expression. transaction_type separates read/write transactions (1) from read-only (2), system (3), and distributed (4) transactions. transaction_state tracks where in its lifecycle a transaction currently sits; state 2 (Active) is the normal case for an open transaction still doing work. State 4 is documented as "for distributed transactions only," and state 5 means the transaction is "in a prepared state and waiting resolution," so both usually point at a distributed transaction waiting on resolution rather than a single session simply forgetting to commit. The detail query's type filter excludes type 4, so those states show up only if the filter is widened.

Computing duration_seconds with DATEDIFF

transaction_begin_time is a plain datetime with no built-in age calculation — DATEDIFF(SECOND, at.transaction_begin_time, SYSDATETIME()) does that arithmetic inline. SYSDATETIME() is used here instead of GETDATE() because it has more fractional-seconds precision, though for a second-level threshold the practical difference is negligible; the more important habit is picking one current-time function and using it consistently across both the filter and the display column, so the WHERE clause and the reported duration never disagree.

Filtering to read/write transactions and a duration threshold

WHERE at.transaction_type IN (1, 3) keeps read/write and system transactions and excludes read-only and distributed ones from the detail query, narrowing the result to the transactions most likely to be the log-truncation culprit — a read/write transaction sitting open holds every log record since its start point. Read-only transactions are left out because they write no log records of their own. A distributed transaction that has written data can hold the log just the same, so remove type 4 from the exclusion if linked-server or MSDTC work is in play. Long read-only transactions under row versioning matter for a different reason: they keep the version store from being cleaned up, and that store lives in tempdb unless accelerated database recovery is enabled, in which case it lives in the user database. The DATEDIFF(SECOND, ...) > 300 predicate is the actual "long-open" threshold; 300 seconds is a reasonable starting point on an OLTP system but should be tuned against what a normal batch job or ETL load on the instance is expected to hold open.

Rolling up to a per-database summary

The second query drops the per-transaction detail and groups by DB_NAME(es.database_id) instead, answering a different question: which database has an open-transaction problem right now, and how bad is it. The database is the session's current database (sys.dm_exec_sessions.database_id), so a session that opened a transaction and then switched context, or one working across databases, is attributed to the database it is in now; sys.dm_tran_database_transactions gives the per-database view of where a transaction actually wrote. COUNT(DISTINCT at.transaction_id) shows how many transactions are past the threshold in that database — plain COUNT(*) would count a transaction once per session when bound or distributed sessions, or MARS, produce multiple join rows — and MAX(DATEDIFF(...)) surfaces the single oldest offender's age. The threshold compares seconds against @ThresholdMinutes * 60, because DATEDIFF(MINUTE, ...) counts minute boundaries crossed and can pass a transaction only a little over four minutes old. Declaring @ThresholdMinutes as a variable rather than hard-coding the literal makes the same script reusable across instances with different tolerance levels without editing the WHERE clause each time.

Key Benefits and Use Cases

  • Names the responsible session — three DMVs join transaction age directly to login_name, host_name, and program_name instead of leaving a bare transaction_id to chase down separately.
  • Separates transaction types — the transaction_type filter keeps read/write and system transactions while excluding read-only and distributed ones, so the result focuses on the transactions most likely to hold the log open.
  • Age comes from a recorded start time — transaction_begin_time gives the start time; DATEDIFF turns it into a duration a threshold or alert can act on directly.
  • Instance-wide in one query — all three DMVs are server-scoped, so a single query surfaces every open transaction across every database without looping database by database.
  • Threshold is a variable, not a hard-coded value — the summary query's @ThresholdMinutes makes the same script reusable across instances with different tolerance for long-running work.
  • No setup required — a pure DMV query with no Extended Events session, trace, or configuration change needed before it returns useful data.

Performance Considerations

  • Memory-only, no history: sys.dm_tran_active_transactions reflects only transactions open at the moment the query runs — once a transaction commits or rolls back it disappears from the view immediately, with nothing retained for later review.
  • Not the only cause of log growth: replication, database mirroring, Always On availability group synchronization, and Change Data Capture can all hold the log active with zero open transactions present — a clean result from this script rules out the transaction-side cause specifically, not log growth generally.
  • Requires VIEW SERVER STATE: querying these DMVs meaningfully needs VIEW SERVER STATE permission at the instance level (SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE instead); on Azure SQL Database the scoped equivalent is VIEW DATABASE STATE, and visibility is limited to the current database's sessions there.
  • Idle-in-transaction looks identical to actively working: a session that opened a transaction and is now sitting idle between statements shows the same transaction_begin_time age as one still actively running a long batch — cross-reference es.status and es.last_request_start_time to tell the two apart before deciding how urgent a given row is.
  • Distributed transactions add extra states: transaction_type = 4 rows can sit in transaction_state 4 or 5 (the commit process initiated, or prepared and waiting resolution), which is a different problem than a single session forgetting to commit and needs different follow-up. The detail query excludes type 4 by default.

Practical Tips

  • Schedule the summary query as a SQL Server Agent job on a short interval — every one to two minutes — and fire a Database Mail alert when open_transaction_count is greater than zero past the chosen threshold.
  • When a row's es.status shows sleeping alongside an old transaction_begin_time, that's the classic forgot-to-commit case — the client application opened a transaction, ran one statement, and never followed up with COMMIT or ROLLBACK.
  • Pull the session's last statement with DBCC INPUTBUFFER(session_id) or by joining sys.dm_exec_requests to sys.dm_exec_sql_text before deciding whether the transaction is safe to leave alone or needs escalation.
  • Log detail-query results to a history table so a spike in long-open transactions can be correlated after the fact with log growth events, VLF count increases, or a specific deployment window.
  • Treat KILL as a last resort, not a first response: Microsoft notes that if the session "has much work to undo, the KILL statement can take some time to complete," so a rollback is not instant and the log stays pinned until it finishes.

Conclusion

Detecting a long-open transaction takes a three-DMV join: sys.dm_tran_active_transactions supplies the transaction and its age, sys.dm_tran_session_transactions supplies the owning session, and sys.dm_exec_sessions resolves that session into a login and application worth calling. Run as a scheduled check with an alert threshold, this script turns "the log won't stop growing" from a mystery into a named session and an exact number of minutes — the starting point for deciding whether to wait, investigate, or escalate.

References

Posts in this series