Accelerated Database Recovery: A Tool, Not a Default

What ADR Changes

Accelerated Database Recovery (ADR) arrived with SQL Server 2019 and changes how the engine rolls back and recovers. Classic recovery has three phases: analysis, redo and undo. Redo and undo both depend on the transaction log, and undo in particular scales with the size of the transactions that were open when the server went down or that you are rolling back right now. A transaction that ran for two hours takes roughly that long to undo.

ADR removes that dependency. Row changes get versions that live in a persistent version store (PVS) inside the database itself. Rolling back means marking the transaction as aborted and letting readers ignore its versions, which is instant. Only a small set of non-versioned operations is tracked in a secondary log stream, the sLog, so crash recovery stays short whatever the transaction size was.

The Two Benefits That Matter

• Rollback and recovery in seconds. Killing a huge UPDATE or restarting an instance that had one running no longer means waiting for the undo phase. A database stuck "in recovery" after a failover becomes available quickly.
• Log truncation is no longer held hostage by long transactions. Without ADR, the log cannot be truncated past the oldest active transaction, and log_reuse_wait_desc shows ACTIVE_TRANSACTION while the file grows. With ADR the log only has to keep what the sLog needs, so one forgotten open transaction no longer fills the drive.

SELECT name,
       log_reuse_wait_desc,
       is_accelerated_database_recovery_on
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

What It Costs

There is no free lunch. The versions have to be stored and cleaned up, and that work moves into your database.

• The PVS needs space and can grow. By default it lives in the PRIMARY filegroup. A cleaner process removes versions that no transaction can still need. If something holds the cleanup back, the PVS grows instead of the log, and the problem has just moved from one file to another.
• Extra I/O on update and delete heavy workloads. Every modified row produces a version. Read-mostly databases barely notice; a staging area that rewrites millions of rows per night does.
• It is per database, and it is a metadata change you should plan. Switching it on or off takes locks on the database. Do it in a quiet period, not while a long batch is running.
• Readable secondaries count too. A long-running query on a readable replica pins versions on the primary and can delay PVS cleanup there.

What Blocks PVS Cleanup

Three things keep versions alive: the oldest active transaction, the oldest snapshot transaction (relevant if the database uses READ_COMMITTED_SNAPSHOT or snapshot isolation), and a long query on a secondary replica. The symptom is always the same, a PVS that only goes up. Watch it directly:

SELECT DB_NAME(database_id)                        AS database_name,
       persistent_version_store_size_kb / 1024      AS pvs_mb,
       current_aborted_transaction_count,
       oldest_active_transaction_id,
       oldest_aborted_transaction_id
FROM sys.dm_tran_persistent_version_store_stats
WHERE persistent_version_store_size_kb > 0
ORDER BY persistent_version_store_size_kb DESC;

If pvs_mb keeps climbing, find the oldest open transaction with sys.dm_tran_active_transactions or DBCC OPENTRAN and deal with it. sp_persistent_version_cleanup triggers a cleanup pass for a database when you want to see whether the store can shrink at all.

So Should It Be a Default?

I would not make it one. A house standard should be something you can switch on everywhere without a second thought, and ADR needs a second thought: you want a monitor on the PVS, you want to know which databases run long transactions, and on a read-mostly database it buys you very little. Switching it on for every database also means adding a new thing to watch to every database.

The better rule is to decide per database:

One more reason to know the feature: newer SQL Server versions build on it. Optimized locking in SQL Server 2025 requires ADR to be on, so on that version the question shifts from "do I want faster recovery" to "do I want optimized locking in this database".

Turning It On

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON;

-- Optionally keep the version store out of PRIMARY
ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON
    (PERSISTENT_VERSION_STORE_FILEGROUP = [PVS]);

For a single database that is fine. For a set of databases after a restore or migration, it fits into the same pass as the other standard settings. In sqmSQLTool, Invoke-sqmDatabaseStandardization brings databases to the house standard (orphaned users, compatibility level, target recovery time, owner). ADR is deliberately not part of that default set. It is an opt-in switch:

# Preview what would change, including ADR
Invoke-sqmDatabaseStandardization -SqlInstance 'SQL01' -Database 'Staging*' -EnableAcceleratedRecovery -WhatIf

# Apply it
Invoke-sqmDatabaseStandardization -SqlInstance 'SQL01' -Database 'Staging*' -EnableAcceleratedRecovery

Databases that already have ADR on are reported as skipped, servers older than SQL Server 2019 are skipped for that step, availability group secondaries are left alone, and the function never turns ADR off again. Without the switch, ADR is untouched.

The Bottom Line

ADR is one of the more useful engine features of the last few releases, because it turns a long outage after a failed batch into a few seconds. It is also a feature with a running cost and a new thing to monitor. Switch it on where long transactions or slow recovery hurt, leave it off where they do not, and put the PVS size on a dashboard the day you enable it.

Related: Log Truncation vs. Log Shrinking explains what holds a log in place, and The Real Fix for an Orphaned Transaction That Fills TempDB covers the same "an open transaction keeps versions alive" effect for the tempdb version store.