Age Open Transactions with sys.dm_tran_active_transactions

calendar Aug 2, 2026 / · 9 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 transaction and flags the ones past a configurable threshold, and a summary rollup that groups by database to show, at a glance, which database has the oldest open 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(*)                                                           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(MINUTE, at.transaction_begin_time, SYSDATETIME()) >= @ThresholdMinutes
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 — a deliberate design, since a transaction can in principle be enlisted across more than one session in a distributed scenario. sys.dm_tran_session_transactions is the bridge: it holds a row correlating each session to its transaction, 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) — the kind that actually hold log records open — from read-only (2), system (3), and distributed (4) transactions, which behave differently for log-truncation purposes. 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, while states 4 and 5 point specifically at a distributed transaction stuck waiting on a two-phase commit resolution rather than a single session simply forgetting to commit.

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 preferred over GETDATE() here for its higher 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) excludes read-only and distributed transactions from the detail query, keeping the result focused on the transactions most likely to be the log-truncation culprit — a read/write transaction sitting open holds every log record since its start point, while a read-only transaction under snapshot isolation affects version store retention in tempdb rather than the transaction log directly. 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: not "which specific session," but "which database has an open-transaction problem right now, and how bad is it." COUNT(*) shows how many transactions are past the threshold in that database, and MAX(DATEDIFF(...)) surfaces the single oldest offender's exact age. 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 isolates read/write transactions from read-only, system, and distributed ones, so the result focuses on the transactions actually capable of holding the log open.
  • Age is exact, not inferred — transaction_begin_time gives a precise start time; DATEDIFF turns it into a duration a threshold or alert can act on directly.
  • Instance-wide in one query — both 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; 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 waiting on two-phase commit resolution across a linked server or MSDTC, which is a different problem than a single session forgetting to commit and needs different follow-up.

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: rolling back a long-running transaction can take longer than the transaction itself ran and generates its own burst of log activity while the rollback completes.

Conclusion

Detecting a long-open transaction is a three-DMV join, not a guess: 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