Accelerated Database Recovery

In one line: Accelerated Database Recovery (ADR, SQL Server 2019+) keeps the previous version of every changed row inside the database, so rollback and crash recovery no longer replay the log backwards. Rollback becomes instant. Writes get slower, and a version store grows inside your database.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/accelerated-database-recovery on GitHub.

What you’ll see

  • Without ADR:
    • A killed batch job that takes longer to roll back than it ran. KILL doesn’t speed it up.
    • A log that fills behind one long transaction.
    • A restart that sits in recovery for an hour.
  • With ADR:
    • Rollbacks that return instantly.
    • A persistent version store taking space in the user database.
    • Tables that grow after big updates.

Why it happens

Without ADR, undoing a transaction means reading its log records backwards. In the demo, rolling back a million-row double update took 2,036 ms. With ADR, the old versions are already stored, so the transaction is just marked aborted: 0 ms. For the same reason the log doesn’t have to be kept to undo an open transaction, so it truncated to 132 MB instead of 640 MB while the transaction was still running.

The price is that every row change also writes the previous version. The same updates took 3× as long (4.4 s against 1.5 s), and left 320 MB of persistent version store.

What to do

  • Turn ADR on where long rollbacks or long recoveries hurt: ALTER DATABASE ... SET ACCELERATED_DATABASE_RECOVERY = ON. Test write-heavy workloads first.
  • Watch the persistent version store, and consider putting it in its own filegroup (PERSISTENT_VERSION_STORE_FILEGROUP).
  • Find and end long-open transactions. One idle transaction stopped the cleaner from removing 320 MB of versions that belonged to a different, already-aborted session.
  • Rebuild tables after large versioned updates if they’ve grown. In the demo the table doubled, from 211 MB to 423 MB, and stayed that way until it was rebuilt.

What doesn’t help

  • Expecting ADR to be free. It moves the cost of undo from rollback time to write time.
  • Ignoring old open transactions. With ADR they pin the version store, just as without it they pin the log.

Check your server

-- ADR status and persistent version store size per database.
SELECT d.name,
       d.is_accelerated_database_recovery_on,
       PvsMB = CAST(pvs.persistent_version_store_size_kb / 1024.0 AS decimal(12, 1)),
       pvs.current_aborted_transaction_count,
       pvs.oldest_active_transaction_id
FROM sys.databases AS d
LEFT JOIN sys.dm_tran_persistent_version_store_stats AS pvs ON pvs.database_id = d.database_id
WHERE d.database_id > 4
ORDER BY PvsMB DESC;

See it happen

The accelerated-database-recovery demo runs the same big transaction with ADR off and on. It measures the rollback, the log under an open transaction, the write cost, the version store, the transaction that blocks its cleanup, and the table’s growth.

Related: why is my transaction log full? · RCSI and snapshot isolation · optimized locking (SQL Server 2025)

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights