{"id":201850,"date":"2026-10-14T04:44:26","date_gmt":"2026-10-14T04:44:26","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=201850"},"modified":"2026-10-14T05:22:45","modified_gmt":"2026-10-14T05:22:45","slug":"accelerated-database-recovery-sql-server-adr-troubleshooting","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/accelerated-database-recovery-sql-server-adr-troubleshooting\/","title":{"rendered":"Accelerated Database Recovery in SQL Server: How ADR Works and How to Troubleshoot Common Issues"},"content":{"rendered":"

In SQL Server versions prior to 2019, the recovery (Full, Bulk-logged, and Simple) rely on the ARIES model for basic database engine operations. Under this data recovery process if a long-running query is killed or if the system shuts down abnormally while a transaction is active, the database engine has to execute a strict three-phase recovery process upon restart. As a result, the transaction enters a prolonged rollback state, which increases database recovery time. To reduce the extended recovery time and long rollback phases, Microsoft introduces Accelerated Database Recovery (ADR) in SQL Server 2019.<\/p>

ADR has completely redesigned the database recovery process to enable immediate transaction rollbacks. In simple words, rolling back a query without ADR causes the database to take more time to recover, whereas rolling back with ADR completes the roll back process quickly.<\/p>

SQL Server 2025 introduces ADR support for tempdb, extending instant rollbacks to temporary workloads. Previously, queries using temporary tables could bloat the transaction log or take a long time to cancel. Enabling ADR for tempdb ensures temporary queries roll back immediately while keeping the transaction log clean and compact.<\/p>

How to Enable ADR in SQL Server?<\/h2>

The ADR feature is not enabled by default in SQL Server. You can check its status by using the following command:<\/p>

SELECT name, is_accelerated_database_recovery_on 

<\/code>FROM sys.databases;<\/code><\/pre>
\"Check<\/figure>
If it shows 0, this means the ADR is disabled.<\/p>
To enable the Accelerated Database Recovery (ADR)<\/strong> feature, you can run the following command:<\/p>

ALTER DATABASE [YourDatabaseName]
SET ACCELERATED_DATABASE_RECOVERY = ON;<\/code><\/code><\/pre>
How ADR Works in SQL Server?<\/h2>
The ADR process follows the same three phases as the traditional recovery process: Analysis, Redo, and Undo. However, it completely redesigns how these phases operate to overcome the performance issues of the old model. In ADR, instead of scanning the transaction log backward from the oldest active transaction, the engine only processes the log from the last successful checkpoint. For this, it introduced new components in these phases: Persistent Version Store (PVS), Logical Revert, and Secondary Log Stream (SLOG). <\/p>
Analysis Phase:<\/strong> During this phase, the engine usually scans the main transaction log to see what was happening after a crash. It checks the successful and failed transactions. But under ADR, this phase is reconstructed with secondary log stream (SLOG). It works in-memory with very low data volume. It stores log records of only non-versioned operations onto the checklist. As it prevents forcing the engine to scan the entire history, it keeps your primary log file small, even a massive transaction is running.<\/p>
Redo Phase:<\/strong> <\/strong>In ADR, the REDO phase is totally changed to reduce the recovery time. It changes the location where the engine starts reading the main transaction log file and divides the single log scan into two sub-phases:<\/p>
    \n
  1. The engine reads only from the SLOG. It reads non-versioned operations from the oldest uncommitted transaction up to the last checkpoints. Since SLOG contains less records, it makes the recovery process fast.<\/li>\n\n\n\n
  2. The engine reads the primary transaction log from the last successful checkpoint to the end to replay all remaining committed operations.<\/li>\n<\/ol>
    Undo Phase:<\/strong> This phase divides the work between the SLOG and Logical Revert. It uses the SLOG to undo only un-versioned operations and Logical Revert via the Persistent Version Store (PVS) to perform row-level, version-based undo. This makes recovery time constant and instantaneous, completely unaffected by the size or duration of long-running transactions.<\/p>
    Previously, canceling a large query could take hours. However, ADR uses Logical Revert to instantly mark transactions as aborted and release all locks immediately, letting users get back to work without waiting. Additionally, its Aggressive Log Truncation feature allows the transaction log to be aggressively truncated even when long-running active transactions exist.<\/p>
    Traditional Recovery Models vs Accelerated Database Recovery (ADR)<\/strong><\/p>
    Here is a table comparing the traditional recovery process with Accelerated Database Recovery (ADR):<\/p>
    Feature<\/strong><\/td>Traditional Recovery (ARIES) Model<\/strong><\/td>Accelerated Database Recovery (ADR)<\/strong><\/td><\/tr>
    Recovery Phase<\/td>Analysis, Redo, and Undo<\/td>Analysis, Redo (2 sub-phases), and Undo<\/td><\/tr>
    Analysis Phase Scope<\/td>Scans forward from the last checkpoint to the end of the log<\/td>Same as traditional, but reconstructs the SLOG and copies non-versioned records<\/td><\/tr>
    Redo Phase Scope<\/td>Scans forward from the oldest uncommitted transaction to the log end<\/td>Divided into two parts: Redo from the SLOGRedo from the last checkpoint to the log end<\/td><\/tr>
    Undo Phase Scope<\/td>Scans the log sequentially. Takes long time for large transactions<\/td>Instantaneous. Uses SLOG and PVS versioning<\/td><\/tr>
    Recovery & Rollback Time<\/td>Long-running transactions cause slow rollbacks and long recovery time<\/td>Rollback becomes a logical operation, not page-by-page undo. Recovery is nearly instantaneous, regardless of transaction size<\/td><\/tr>
    Version Storage Location<\/td>Use tempdb for traditional row-versioning workloads<\/td>Stores row versions inside the user databaseRow‑level undo mechanism Version‑based undo <\/td><\/tr>
    Transaction Log Size<\/td>As the log cannot be truncated, there is high risk of bloat<\/td>Less risk of transaction log bloat as the log is truncated aggressively<\/td><\/tr><\/tbody><\/table><\/figure>
    Benefits of SQL Server Accelerated Database Recover (ADR)<\/h2>
    Enabling ADR in SQL Server provides several advantages for database performance and availability such as:<\/p>
    Fast and Consistent Database Recovery:<\/strong> When ADR is enabled, it scans only a minimal, fixed portion of the transaction log using the SLOG. This makes rollback instantaneous and crash recovery faster, regardless of the size or number of active transactions in the system.<\/p>
    Instantaneous Transaction Rollback:<\/strong> It does not take long time to roll back\/cancel the lengthy transaction. It completes an active transaction immediately, no matter how long the transaction has been running or how many updates it made.<\/p>
    Aggressive Log Truncation:<\/strong> Prevents the transaction log from growing out of control by aggressively truncating it even when active, long-running transactions are present.<\/p>
    Instant Lock Release:<\/strong> The internal Logical Revert process releases all database locks immediately after a transaction is aborted, preventing blocking issues.<\/p>
    Resource Isolation and Availability:<\/strong> As it stores row versions directly in the database itself (via PVS) rather than using shared tempdb storage, it enables better resource isolation and improves the availability of readable secondary databases.<\/p>
    Better Database Performance:<\/strong> ADR skips the time-consuming, row-by-row reading by using the SLOG in the redo phase. The SLOG accelerates this phase because it processes only non-versioned operations. This increases database performance.<\/p>