The batch job had been running for six hours and fourteen minutes when the on-call engineer killed it. It was a quarterly settlement process writing against a 900-million-row ledger table, and at hour five someone realized it was running against the wrong period. KILL 73 felt decisive. What followed was not: the rollback took four hours and forty minutes, longer than the first half of the job itself, holding every lock the batch had taken for the entire duration. The ledger table was effectively down until after lunch. SQL Server showed KILLED/ROLLBACK with percent_complete creeping upward, and there was nothing to do but watch it climb and write estimates into the incident channel.
Years later I watched the same class of kill — a four-hour report-generation batch, roughly 30 GB of logged work — return the database in about eight seconds. The undo had not happened yet. It did not need to. The database had accelerated database recovery enabled, and the difference was not a faster rollback but a rollback that mostly never executes. That trade, and the new meter it installs in your storage layer, is the whole story of this article. Everything here applies to SQL Server 2019 and later, where ADR first shipped.
How did the old recovery model make rollback so expensive?
The old model is ARIES, and it makes rollback cost roughly what the transaction cost to run. Recovery — whether after a crash or after a KILL — walks three phases: analysis, which scans the log to rebuild the dirty-page and active-transaction tables; redo, which replays every logged change so the data files match the log; and undo, which rolls back every transaction that never committed. Redo is bounded by how far the log has outrun the data files, and checkpoints keep that gap small. Undo is the unbounded phase: it is proportional to the work of the longest uncommitted transaction, because SQL Server reverses a transaction by walking its log records backward and physically undoing each change, one compensating log record at a time, newest to oldest.
That proportionality is why a six-hour batch rolls back for nearly six hours, and it is why crash recovery had the same disease: restart an instance one hour into a twelve-hour batch and the database sits in recovery until the undo phase clears. During a KILL's rollback the session also holds its locks the whole time, which is exactly why the kill-or-wait framework in my blocking chain notes asks "what does rollback cost?" before it asks anything else. Under ARIES, the answer was often "as long as the job ran, with the locks held."
What does accelerated database recovery actually change?
ADR replaces physical undo with a persisted version store in the user database, plus a logical revert that makes rollback and crash recovery near-constant-time. Row versions that the classic model would have kept in tempdb — for snapshot isolation and the like — and the versions ADR needs for undo now live in a version store carved out of the user database itself, the PVS. When a transaction modifies a row, the previous version is written to the PVS and the row points at it. Undoing a change stops meaning "replay the log backward and physically reverse each write." It becomes logical revert: flip the row back to the last committed version from the store, which is a pointer operation per row, not a replay of every logged change. A transaction that ran for six hours reverts in about the same time as one that ran for six seconds.
Crash recovery changes shape too. The engine keeps a small in-memory log stream alongside the regular log so it can reconstruct what was in flight without scanning, redo replays only from the last checkpoint, and undo — the phase that used to hold the database hostage — defers entirely: the database comes online as soon as redo finishes, and aborted transactions revert in the background using the PVS. The third leg is aggressive log truncation: because versions for undo live in the PVS instead of being reachable only through the log, the log records belonging to aborted transactions stop pinning the log. A killed twelve-hour batch no longer holds twelve hours of log hostage either; the engine can truncate aggressively while only the oldest active transaction keeps its claim on log space.
How do I enable accelerated database recovery?
One ALTER DATABASE per database, and it is online: no restart, no exclusive access required, and it takes effect immediately. ADR is off by default on SQL Server 2019 through 2022 on-prem and in most self-managed setups, and on by default in Azure SQL Database — which tells you what Microsoft's own operations teams think of the trade-off. You verify it on sys.databases:
ALTER DATABASE [Ledger] SET ACCELERATED_DATABASE_RECOVERY = ON;
SELECT name, is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = 'Ledger';The setting is per-database, not per-instance, so you pick your battles: the ledger database that eats the quarterly batch gets it, the scratch database the BI team uses for temp analysis probably does not need the ceremony. One asymmetry worth knowing before you ever need it: turning ADR on is instant, but turning it off is not. Disabling has to wait for the persisted version store to be cleaned out, so the ALTER can sit waiting on the version cleaner if the PVS is large. Do not treat it as a switch you flip during an incident — treat the enable decision as a one-way-ish configuration change and plan the storage for it, which brings us to the cost sheet.
What does ADR cost once it is on?
Storage, mostly: the version store lives in your user database now, and it grows with churn and shrinks only when the background cleaner can reach the versions. Every update and delete against a database with ADR on writes a row version into the PVS, so a database doing heavy in-place churn on hot rows carries that extra write and extra space continuously. The classic model paid some of this in tempdb; ADR moves the bill into the database file itself, where it competes with your data for the same disks and the same capacity alerts. On the settlement database, steady state added a few percent, but the day after the eight-second kill the PVS was holding tens of gigabytes of versions from the aborted batch until the cleaner worked through them.
The DMV to watch is sys.dm_tran_persistent_version_store_stats, which reports the store size per database:
SELECT DB_NAME(database_id) AS database_name,
persistent_version_store_size_kb / 1024 AS pvs_size_mb,
online_index_version_store_size_kb / 1024 AS index_pvs_mb,
oldest_transaction_begin_time,
aborted_version_cleaner_start_time,
aborted_version_cleaner_end_time
FROM sys.dm_tran_persistent_version_store_stats
WHERE database_id = DB_ID('Ledger');Two failure modes matter. First, aborted-transaction cleanup: a killed transaction's versions are not free space until the background version cleaner processes them, and you can watch that lag in the aborted_version_cleaner columns above — a cleaner start time that never advances after a big kill means versions are piling up. Second, the pinning problem: the cleaner cannot remove versions that an active transaction might still need, so the oldest active transaction sets the floor for cleanup. One forgotten session that opened a transaction nine days ago — the same sleeping-session disease from the blocking article — can pin the PVS and let it grow unbounded, and the only fix is closing that transaction. Check sys.dm_tran_database_transactions for old transaction_begin_time values whenever the PVS trend line points the wrong way. The classic tempdb version store has the identical pathology, by the way — ADR moved the disease into a file you now own and have to size.
When should I leave accelerated database recovery off?
Leave it off when you would pay the meter without ever cashing the benefit. The benefit is bounded-rollback and bounded-recovery time; the price is per-write version overhead plus storage that must be provisioned and monitored. A database that never runs long transactions, never gets its sessions killed, and sits on hardware where crash recovery has always been a thirty-second event gains almost nothing. A heavily churned OLTP database with tiny rows and extreme update rates pays the version-write overhead on a hot path where every extra write shows up in latency percentiles. And a database already pressed against its storage quota, with no headroom for a version store that can spike to a meaningful fraction of the data size after a big kill, is a bad candidate until the disks grow. The honest summary: enable it where transactions are long, kills and crashes are operationally expensive, and storage has slack — settlement batches, reporting pipelines, data warehouses with big ETL transactions. Skip it where transactions are short, storage is tight, and recovery has never been your incident.
Watching the version store with MonPG when SQL Server support lands
The counters this story actually needs on a dashboard are the PVS size per database over time, the oldest active transaction age, the version cleaner's last progress timestamp, and log truncation status next to log growth — the four numbers that separate "ADR is quietly doing its job" from "the version store is about to fill the volume at 3 AM." MonPG monitors PostgreSQL in production today, and SQL Server support is on the roadmap and in active development; the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, the two DMVs above on a schedule, writing into a table you chart yourself, is a perfectly good monitoring stack — the important thing is that someone looked at the PVS trend before the volume filled, not after.