Accelerated Database Recovery (ADR): The Rollback Is Instant, You Pay Later

This page explains how that works, what it costs and what it has to do with locks. After all, since SQL Server 2025, ADR is the prerequisite for optimized locking.

In short

1. ADR moves row versions out of tempdb and into the database, into the persistent version store (PVS). They are logged and survive a restart.
2. Rollback is logical: The transaction is marked as aborted, readers skip its versions, and cleanup happens in the background.
3. Recovery after a restart no longer depends on the longest open transaction. In the lab, 0.22 to 0.39 seconds instead of 1.6 to 2.3 seconds, with transactions of about 150 MB of log.
4. The log can be truncated, even while a long transaction is running. No more ACTIVE_TRANSACTION as a wait reason for this transaction.
5. The price: space for the PVS, more log volume, write load from versioning; in our extreme case, the UPDATE itself was three to four times slower with ADR.
6. Available since 2019 in Enterprise, Standard and Web; since 2025 the prerequisite for optimized locking.

What happens in a rollback without ADR

Every change writes a log record. A rollback reads these records backward and undoes each change individually: row by row, page by page, logged as compensation. That is why a rollback takes about as long as the work it undoes, and why the transaction holds all its locks in the meantime.

The same applies after a crash or restart: recovery replays the log from the last checkpoint (redo) and rolls back everything that was open at the time of the crash (undo). The longest open transaction determines how far back the log has to be read. A single forgotten transaction with many changes can thus delay the restart of an entire database by minutes. And as long as it is open, the log may not be reused from its beginning onward: log_reuse_wait_desc = ACTIVE_TRANSACTION. The same recovery is part of the failover time of a failover cluster instance or availability group; part 2 of the series "FCI vs Availability Groups" shows it measured in the lab.

In the lab, an open transaction with three updates over 100,000 rows held on to about 200 to 240 MB of log. What else can hold on to the log is covered in SQL Server Transaction Log Full: Fix Error 9002.

What ADR does differently

ADR builds on the versioning mechanism that SQL Server has had since 2005 for RCSI and snapshot isolation, with two changes:

• The location. Row versions no longer live in tempdb but in the persistent version store in the user database. Short versions go in the row itself (in-row), others in a separate structure (off-row). They are logged and survive a restart.
• The consequence. For every changed row, it is permanently known which transaction wrote it last and whether that transaction is still running. An aborted transaction does not have to write anything back: it is tracked in an abort list, and whoever reads its version takes the one before it instead. This is the logical revert. Physical cleanup happens later, by the cleaner, which has used multiple threads since SQL Server 2022.

For operations that cannot be versioned, such as changes to allocation structures, ADR introduces a small second log (SLOG) in memory, which is persisted at checkpoint. This keeps recovery short even for DDL.

ADR explicitly does not change locking semantics. If you expect less blocking from ADR, you will be disappointed; that is the job of RCSI and optimized locking.

What it delivered in the lab

The same transaction, three updates over 100,000 rows each, then ROLLBACK; four versions, three runs each:

The last row is the price. In LAB-10 on SQL Server 2025, the rollback of 100,000 rows took 302.94 ms without and 0.69 ms with ADR; there, too, the UPDATE itself was no faster with ADR. The test is an extreme case: a large UPDATE on a small table, many versions per page. For mixed workloads, Microsoft's research paper on ADR cites 2.4 to 13.8 percent less throughput.

On Nov 8, 2026, we repeated both patterns on SQL Server 2022 CU27 and 2025 CU10, three runs each: the rollback of the large transaction took 2.48 to 2.62 seconds without ADR and 0.10 to 0.24 milliseconds with ADR; the simple rollback of an int change over 100,000 rows took 240 to 253 milliseconds compared with 0.09 to 0.12 milliseconds. Two observations on the side: The first run with ADR on a freshly loaded table wrote 53.9 MB of log instead of 10 to 17 MB afterward, because the first change enlarges the records and splits pages. And the small int change produced no growth in the off-row PVS: ADR keeps such versions in the record itself; the 200-byte change, by contrast, produced about 92 MB per transaction.

What ADR costs, and where you see it

Space in the PVS. In the version store lab, for ten runs over 100,000 rows, 253 MB of versions sat in the database's PVS instead of 279 MB in the tempdb version store. So the space only moves; it does not disappear. It now sits in the database that you back up and restore.

Log. Versions are logged. In the same lab, the transaction log used 491 MB instead of 142 MB.

Pages. As soon as rows are versioned, up to 14 bytes of version information per row are added; in the lab, the table had twice as many pages with ADR because of page splits, and the writers took just over twice as long.

Cleanup. The cleaner runs periodically. As long as a transaction could still see the versions, such as an open snapshot reader, it cleans up nothing; in the lab, it skipped 32,417 pages in that situation. After the holder ended, the versions disappeared on the next cycle, after about a minute. Long or aborted transactions therefore make the PVS grow; sys.dm_tran_persistent_version_store_stats shows size, abort list and cleaner status.

Who is holding on to the versions right now is shown by Query 2 of the tempdb hub, row versions and log for all databases; from SQL Server 2019, it also reads the persistent version store. We deliberately did not create a PVS query of our own here.

Enabling, checking, rolling back

-- Enable ADR, optionally with a dedicated filegroup for the PVSALTER DATABASE [MyDatabase]    SET ACCELERATED_DATABASE_RECOVERY = ON    (PERSISTENT_VERSION_STORE_FILEGROUP = [PVS_FG]); -- CheckSELECT name, is_accelerated_database_recovery_onFROM sys.databasesWHERE name = N'MyDatabase';

• The change needs a moment without other active connections in the database; plan a maintenance window.
• A dedicated filegroup for the PVS is worthwhile if you want to watch the space separately or place it on different storage.
• To roll back, use OFF, but only if optimized locking is off: otherwise SQL Server 2025 reports Msg 12134 (“Optimized Locking is enabled for this database. To disable Accelerated Database Recovery, disable Optimized Locking and try again.”).
• SQL Server 2017 does not know the option (Msg 102, “Incorrect syntax near 'ACCELERATED_DATABASE_RECOVERY'.”).
• Editions: since 2019 in Enterprise, Standard and Web.

When to turn ADR on

Reasons in favor: long transactions whose rollback or recovery has already cost you minutes, a log that grows because of ACTIVE_TRANSACTION, and the wish for optimized locking in SQL Server 2025. Reasons against: database and log space that is already tight, very write-heavy tables with small rows, where the 14 bytes and the versions matter, and workloads with many long-open snapshot readers that slow down the cleaner. In all cases: measure on your workload, observe the PVS and log for a week, then decide.

Frequently asked questions

Does ADR make my queries faster? No. ADR makes rollback and recovery fast and makes the log independent of long transactions. Reading and writing tend to become a little more expensive.

Does ADR need RCSI? No. ADR versions on its own; RCSI and snapshot isolation then use the same PVS instead of tempdb.

Does my PVS grow without limit? Only as long as something holds on to the versions. Find the open transaction or the snapshot reader; sys.dm_tran_persistent_version_store_stats shows the oldest transaction being held.

Does ADR help in tempdb? Yes, since SQL Server 2025. The rollback of a #temp change finishes instantly, but under many short temp tables it costs a lot of throughput; the measurements are in ADR for tempdb.

When rollback and recovery are your problem

Whether ADR delivers the expected benefit in your environment and what it costs in space and log can be answered with a week of observation. We do this as part of our performance analysis, or we check in the SQL Server Health Check which of your databases would benefit.

More locking topics