SQL Server & Data

Under the Hood: Constant Time Recovery and Accelerated Database Recovery

Microsoft SQL evolved its recovery mechanisms to decouple recovery time from transaction size using Persistent Version Store and Constant Time Recovery (CTR). This design addresses traditional ARIES limitations, drastically reducing tail-latency recovery durations and transaction log growth. The technology ships as Accelerated Database Recovery in Azure SQL Database and SQL Server.

What changed From the source

Traditional SQL Server engines relied on the ARIES recovery algorithm, which requires walking backward through transaction logs during the undo phase to restore a consistent state. Because the cost of this recovery was directly proportional to the amount of uncommitted work, large bulk loads or failed transactions could cause hours of database unavailability.

To solve this, Microsoft introduced Constant Time Recovery (CTR), detailed in a 2019 VLDB paper and shipped as Accelerated Database Recovery (ADR). CTR combines traditional ARIES with multi-version concurrency control by introducing a durable Persistent Version Store (PVS) directly inside the database. Small changes are maintained as in-row byte diffs, while larger changes reside in an off-row table.

When a transaction aborts under CTR, the database marks the transaction ID in an in-memory Aborted Transaction Map rather than walking the log. Subsequent queries skip these aborted row versions and read previous versions instead, making rollbacks and aborts nearly instantaneous while background processes clean up lazily. A secondary log stream called the SLog handles unversioned operations like metadata changes and lock acquisitions.

Production data from Azure SQL Database shows dramatic improvements at high percentiles, dropping 99.999th percentile recovery time from over an hour down to three minutes. Furthermore, log truncation can occur aggressively while massive transactions run, reducing transactional log consumption drastically. CTR was enabled worldwide for over three million databases in Azure SQL Database in May 2019, and is available in SQL Server since 2019.

Subsequent developments improved the version store and cleanup mechanisms. SQL Server 2022 introduced multi-threaded cleanup, version store cleanup for committed transactions, assistance from user transactions when background cleanup is blocked, and reduced version tracking memory. SQL Server 2025 further extends ADR to tempdb, preventing large rollbacks involving temp tables from taking excessive time.

  • Traditional recovery cost was proportional to transaction size, leading to extended recovery times after unexpected failures.
  • CTR utilizes a Persistent Version Store (PVS) and an Aborted Transaction Map to make transaction aborts instant.
  • An SLog manages unversioned metadata and lock acquisitions in constant time.
  • 99.999th percentile recovery times improved from over an hour to three minutes in production Azure SQL Database measurements.
  • Transaction log size for large operations dropped significantly, such as 50 million inserts consuming 185 MB instead of 25,873 MB.
  • SQL Server 2022 and SQL Server 2025 added multi-threaded cleanup, committed transaction cleanup, and tempdb ADR extension.

At a glance · SQL Server & Data

What changedWhy it mattersWho should careUpgrade and migration considerations

Why it matters Analysis

The shift from work-proportional recovery to constant-time recovery addresses a core availability bottleneck in relational databases. By preventing large uncommitted transactions from holding up database recovery or bloating transaction logs, systems can maintain predictable SLAs even after unexpected reboots or aborted operations.

The engineering approach demonstrates that foundational database engines can be modernized without a complete rewrite, integrating multi-version concurrency control into the existing ARIES framework via targeted components like the PVS, Aborted Transaction Map, and SLog.

Additionally, ADR serves as a architectural dependency for newer engine capabilities. For instance, optimized locking frameworks in Azure SQL rely directly on the persistent version store infrastructure introduced by these recovery improvements.

  • Eliminates unpredictable, hour-long recovery windows following unexpected system reboots.
  • Prevents long-running data loads or runaway updates from exhausting transaction log space.
  • Serves as a necessary architectural prerequisite for advanced features such as optimized locking.

Who should care Analysis

Database administrators, cloud engineers, and developers managing high-volume transactional workloads benefit directly from predictable recovery times and minimized maintenance overhead. Applications that handle large data ingestion, bulk loads, or frequent batch updates are particularly vulnerable to traditional log expansion and lengthy rollback times.

Developers operating on Azure SQL Database benefit automatically because ADR is always enabled. Those managing self-hosted SQL Server instances must evaluate whether to enable ADR based on their workload characteristics and version deployment.

  • DBAs and operations teams seeking predictable failover and recovery SLAs.
  • Developers running high-throughput batch insert, update, or delete workloads.
  • Engineers managing environments where transaction log exhaustion has historically caused outages.

Upgrade and migration considerations Analysis

While ADR is permanently enabled in Azure SQL Database, it is turned off by default for self-hosted SQL Server environments. Organizations running self-hosted SQL Server should note that enabling ADR introduces a performance trade-off in database throughput.

According to the source, benchmark testing on workloads like TPC-C showed measurable throughput impacts, making it essential to test workloads prior to adoption. Furthermore, because early versions of the version store required single-threaded tuning, upgrading to recent releases like SQL Server 2022 or newer is recommended to leverage multi-threaded cleanup enhancements before enabling ADR on busy databases.

  • ADR is always on in Azure SQL Database but requires manual opt-in for self-hosted SQL Server instances.
  • Workload throughput can be impacted, showing performance trade-offs that vary by transaction type.
  • Upgrading to recent SQL Server releases is advised to utilize multi-threaded background cleanup improvements.

Sources and further reading

Original: “How Microsoft SQL learned to recover without undoing everything first”, Azure SQL Dev Corner, published 2 Nov 2026, retrieved 2 Nov 2026. This article was drafted with AI assistance from the official source and passed automated fact and quality checks. Spotted a mistake? See our corrections policy.