Version Store and Transaction Log: What an Open Transaction Holds On To

This transaction is not doing any work. But it holds on to what others produce: row versions that it could still see, and log that may not be reused from the moment it began. Where that ends up depends on whether Accelerated Database Recovery (ADR) is turned on.

Where row versions live

SQL Server creates row versions when a database uses Read Committed Snapshot (RCSI) or snapshot isolation. Triggers, online index operations, and MARS also create versions. Depending on the configuration, they are stored in different places:

From SQL Server 2025, ADR can also be turned on for TempDB itself. We have not tested that.

What holds on to versions

Versions stay for as long as a transaction could still read them. With snapshot isolation, that is the oldest open snapshot transaction; with RCSI, it is the longest-running statement. If the rest of the application keeps writing in the meantime, the version store grows with every change.

In the lab, one session read data in SNAPSHOT isolation once and then left its transaction open for 150 seconds without changing anything itself. Meanwhile, a second session changed all 100,000 rows of a table ten times, committing after each round.

The assignment is unambiguous: without ADR, all versions ended up in TempDB; with ADR, not a single one did. As long as the reading transaction was open, everything stayed in place, even though the cleanup ran several times. After it ended, TempDB was empty after 17 to 20 seconds. The PVS cleaner works on a fixed interval of one minute and released everything on its first pass afterward.

Important for diagnosis: TempDB space used for versions is not attributed to the session that holds on to it. The session that holds everything back often has not allocated a single page in TempDB itself. You find it through the open snapshot transactions and their age, not through space usage per session.

What holds on to the log

In the SIMPLE recovery model, a checkpoint frees the part of the log that is no longer needed. From the start of the oldest open transaction onward, however, nothing is freed: SQL Server might still have to roll that transaction back. sys.databases then shows ACTIVE_TRANSACTION as the wait reason. This applies to user databases just as it does to TempDB itself.

In the lab, one transaction kept a single changed row open. A second session changed 5,000 rows at a time, 60 times, committed each time, and ran a checkpoint after each commit:

Without ADR, the log stayed fully in use until the end of the measurement. With ADR, it was gradually freed again from about 39 seconds on and ended at 3.1 MB, even though the transaction was still open. ADR no longer needs the log to roll this transaction back, because the old versions are in the PVS. You only see the effect after a while: in a shorter test with twelve rounds, the wait reason still showed ACTIVE_TRANSACTION in both variants.

Rollback: back quickly, cleanup comes later

ADR also changes how rollback works. After 100,000 changed rows, ROLLBACK returned in the lab after a median of 303 ms without ADR and 0.7 ms with ADR. SQL Server does not have to write the old values back; they are already in the PVS. The PVS cleaner does the physical cleanup later in the background.

ADR is not a switch without a price. The versions take up space in the user database, and writing them costs work. In the version store test, the log with ADR used 491 MB instead of 142 MB, the writer needed 5 to 6 seconds instead of 2 to 3, and the table afterward had twice as many pages, each only half full, even after the PVS was empty again. We did not investigate where exactly the doubling comes from. In our lab with concurrent writers that deliberately scanned tables without a suitable index, the throughput of a single writer with ADR alone fell from 68 to 53 changes per second, and the container's CPU time per change rose to almost three times. Only together with RCSI and Optimized Locking (SQL Server 2025) did this reverse with eight concurrent writers. That is a deliberately sharpened case, not a general rate. But it shows that ADR should be tested with your own workload before you turn it on.

How to find the holding transaction

The following query shows, for all databases on the instance, which versioning is active, why the log is not being freed, how large the PVS is (SQL Server 2019 and later), and which transactions hold on to versions and log the longest. For TempDB itself, use the TempDB snapshot on the overview page.

/* PSG Stack · SQL Server TempDB · Query 2
   Who is holding on to row versions and log? All databases on the instance.

   - Read-only. Changes nothing, ends no session.
   - Returns four result sets:
       1 Databases: isolation options, ADR, log wait reason
       2 Oldest open transactions with log, per database
       3 Persistent version store (only SQL Server 2019 and later, with ADR)
       4 Oldest snapshot transactions, including pure readers
   - Permissions: VIEW SERVER STATE
     (from SQL Server 2022, VIEW SERVER PERFORMANCE STATE is enough).
     Without this permission, result 1 still returns rows, result 2 fails with error 300,
     and result 3 with error 262 (for result 3, VIEW DATABASE STATE is also enough).
   - SQL Server 2017 and later. Tested in the PSG lab on 2017, 2019, 2022, and 2025.
     Result 3 does not apply on SQL Server 2017.
*/

/* 1 · Databases: RCSI, snapshot isolation, ADR, and the log wait reason */
DECLARE @adr nvarchar(100) =
    CASE WHEN COL_LENGTH('sys.databases', 'is_accelerated_database_recovery_on') IS NOT NULL
         THEN N'd.is_accelerated_database_recovery_on' ELSE N'CAST(NULL AS bit)' END;
DECLARE @sql nvarchar(max) = N'
SELECT
    d.name                                   AS database_name,
    d.is_read_committed_snapshot_on          AS rcsi,
    d.snapshot_isolation_state_desc          AS snapshot_isolation,
    ' + @adr + N'                            AS adr,
    d.recovery_model_desc                    AS recovery_model,
    d.log_reuse_wait_desc                    AS log_wait_reason
FROM sys.databases AS d
WHERE d.state = 0
ORDER BY d.name
OPTION (MAXDOP 1);';
EXEC sys.sp_executesql @sql;

/* 2 · Oldest open transactions with log, per database
   Pure readers in SNAPSHOT isolation do not appear here: they have not written any log
   (database_transaction_begin_time is NULL). Result 4 shows them. */
SELECT TOP (20)
    DB_NAME(dt.database_id)                                               AS database_name,
    st.session_id,
    dt.database_transaction_begin_time                                    AS began_at,
    DATEDIFF(SECOND, dt.database_transaction_begin_time, SYSDATETIME())   AS open_s,
    CAST(dt.database_transaction_log_bytes_used / 1048576.0 AS decimal(12,1)) AS log_used_mb,
    es.status,
    es.login_name,
    es.program_name,
    es.last_request_end_time                                              AS last_request_end
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_exec_sessions AS es ON es.session_id = st.session_id
WHERE dt.database_transaction_begin_time IS NOT NULL
ORDER BY dt.database_transaction_begin_time
OPTION (MAXDOP 1);

/* 3 · Persistent version store (ADR), SQL Server 2019 and later
   Who holds on to the versions is shown by result 2 (writing transactions) and result 4 (snapshot transactions). */
IF OBJECT_ID('sys.dm_tran_persistent_version_store_stats') IS NOT NULL
    EXEC sys.sp_executesql N'
    SELECT
        DB_NAME(pvs.database_id)                                      AS database_name,
        CAST(pvs.persistent_version_store_size_kb / 1024.0 AS decimal(12,1)) AS pvs_mb
    FROM sys.dm_tran_persistent_version_store_stats AS pvs
    WHERE pvs.persistent_version_store_size_kb > 0
    ORDER BY pvs.persistent_version_store_size_kb DESC
    OPTION (MAXDOP 1);';

/* 4 · Oldest snapshot transactions: they hold on to versions in TempDB and in the PVS,
   even if they only read. snapshot_isolation = 0: transaction without SNAPSHOT isolation
   that creates row versions (e.g., writers under READ_COMMITTED_SNAPSHOT). */
SELECT TOP (10)
    ast.session_id,
    ast.elapsed_time_seconds                      AS open_s,
    ast.is_snapshot                               AS snapshot_isolation,
    es.login_name,
    es.program_name,
    es.status,
    es.last_request_end_time                      AS last_request_end
FROM sys.dm_tran_active_snapshot_database_transactions AS ast
LEFT JOIN sys.dm_exec_sessions AS es ON es.session_id = ast.session_id
ORDER BY ast.elapsed_time_seconds DESC
OPTION (MAXDOP 1);

How to read the result:

• log_wait_reason = ACTIVE_TRANSACTION: The matching transaction appears near the top of result 2. If its status is “sleeping” and last_request_end was a long time ago, nobody is waiting on this transaction; it simply was not ended.
• Versions keep growing, in TempDB or in the PVS: Result 4 shows the holding transaction, the oldest snapshot transaction, even if it only reads. A pure reader does not appear in result 2 because it has not written any log. In the lab, it was visible only in result 4.
• pvs_mb no longer shrinks: The PVS cleaner cannot clean up as long as result 2 or 4 shows an old transaction. A large value alone does not prove that versions are being held back, though: in one run, pvs_mb rose to about 1 GB after cleanup with no further changes, apparently space reserved in advance. We have not clarified the cause.

What helps in the long run

• Keep transactions short. No user input, file access, or external calls inside an open transaction. Applications that pool connections should reliably finish transactions, including on errors (SET XACT_ABORT ON).
• Use long readers deliberately. A report in SNAPSHOT isolation that runs for an hour holds on to versions for an hour. If it runs on a readable copy, the problem shifts; it does not go away.
• Monitor what holds on. An alert on old open transactions and on log_reuse_wait_desc reports the problem before TempDB or the log is full.
• Evaluate ADR deliberately. ADR moves versions out of TempDB into the database, shortens rollbacks, and lets the log be freed despite an open transaction. In return, it costs space and write work in the database. Test it with your workload before you turn it on.
• Plan TempDB generously. That buys time, but it does not replace ending the holding transaction.

Sources

• Microsoft: Transaction Locking and Row Versioning Guide
• Microsoft: Accelerated Database Recovery (ADR) and Monitor and Troubleshoot Accelerated Database Recovery
• Microsoft: The Transaction Log (including reasons for delayed log truncation)
• Microsoft: tempdb Database
• Microsoft: sys.dm_tran_persistent_version_store_stats

More TempDB topics