Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://learn.microsoft.com/en-us/azure/azure-sql/accelerated-database-recovery below:

Accelerated Database Recovery (ADR) - SQL Server

Applies to: SQL Server 2019 (15.x) and later versions Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric Preview

Accelerated database recovery (ADR) improves database availability, especially in the presence of long-running transactions, by redesigning the database engine recovery process.

ADR was introduced in SQL Server 2019 (15.x) and improved in SQL Server 2022 (16.x) and SQL Server 2025 (17.x) Preview. ADR is also available in Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (dedicated SQL pool only), and SQL database in Microsoft Fabric Preview.

Note

ADR is always enabled in Azure SQL Database, Azure SQL Managed Instance, and SQL database in Fabric.

This article provides an overview of ADR. To work with ADR, review Manage accelerated database recovery.

For more information about transaction log and database recovery, see SQL Server transaction log architecture and management guide and Restore and recovery overview (SQL Server).

The primary benefits of ADR are:

• Fast and consistent database recovery

  Long-running transactions don't affect the overall recovery time, enabling fast and consistent database recovery irrespective of the number of active transactions in the system or their size.

• Instantaneous transaction rollback

  Transaction rollback is instantaneous, irrespective of the time that the transaction has been active or the number of updates that have been made.

• Aggressive log truncation

  The transaction log is aggressively truncated, even in the presence of active long-running transactions, which prevents it from growing out of control.

Without ADR, database recovery follows the ARIES recovery model and consists of three phases, which are illustrated and explained in more detail in the following diagram:

• Analysis phase

  The database engine performs a forward scan of the transaction log from the beginning of the last successful checkpoint (or the oldest dirty page log sequence number (LSN)) until the end, to determine the state of each transaction at the time the engine stopped.

• Redo phase

  The database engine performs a forward scan of the transaction log from the oldest uncommitted transaction to the end. This process redoes all committed operations to restore the database to its state at the time of the crash.

• Undo phase

  For each transaction that was active at the time of the crash, the database engine traverses the log backward, undoing the operations that this transaction performed.

• With the traditional database recovery process, recovery after a crash can take a long time if a long-running transaction was active.

  The time for the database engine to recover from an unexpected restart is (roughly) proportional to the size of the longest active transaction in the system at the time of the crash. Recovery requires a rollback of all incomplete transactions. The length of time required is proportional to the work that the transaction has performed and the time it has been active.

• Canceling, or rolling back, a large transaction can take a long time, because it uses the same undo recovery phase as described previously.

• The database engine can't truncate the transaction log when there are long-running transactions because their corresponding log records are needed for the recovery and rollback processes. As a result, the transaction log can grow very large and consume a lot of storage space.

ADR addresses previous issues with the traditional recovery model by completely redesigning the database engine recovery process to:

• Make the recovery time constant since there is no longer a need to scan the transaction log from the beginning of the oldest active transaction. With ADR, the transaction log is only processed from the last successful checkpoint (or oldest dirty page LSN). As a result, recovery time isn't affected by long-running transactions and is typically instantaneous.

• Minimize the required transaction log space since there's no longer a need to retain the log for the whole transaction. As a result, the transaction log can be truncated aggressively as checkpoints and backups occur.

At a high level, ADR achieves fast database recovery by versioning all physical database modifications and only undoing nonversioned operations, which are limited and can be undone almost instantly. Any transactions that were active at the time of a crash are marked as aborted and, therefore, any versions generated by these transactions can be ignored by concurrent user queries.

The ADR recovery process has the same three phases as the traditional recovery process. How these phases operate with ADR is illustrated in the following diagram:

• Analysis phase

  The process remains the same as the traditional recovery model with the addition of reconstructing the secondary log stream (SLOG) and copying log records for nonversioned operations.

• Redo phase

  Broken into two subphases

  • Subphase 1

    Redo from SLOG (oldest uncommitted transaction up to the last checkpoint). Redo is a fast operation as it might only need to process a few records from the SLOG.

  • Subphase 2

    Redo from transaction log starts from last successful checkpoint (instead of oldest uncommitted transaction).

• Undo phase

  The undo phase with ADR completes almost instantaneously by using SLOG to undo nonversioned operations and persistent version store (PVS) using logical revert to perform row level version-based undo.

For an explanation of accelerated database recovery, watch this eight-minute video:

The four key components of ADR are:

• Persistent version store (PVS)

  The persistent version store (PVS) is a database engine mechanism for persisting row versions in the database itself instead of in the traditional version store in the tempdb database. PVS enables resource isolation and improves availability of readable secondaries.

  PVS stores row versions either directly on data pages being modified, or in a separate system table. For more information, see Space used by the persistent version store (PVS).

• Logical revert

  Logical revert is the asynchronous process responsible for performing row-level version-based undo - providing instant transaction rollback and undo for all versioned operations.

  • Keeps track of all aborted transactions
  • Performs rollback using PVS for all user transactions
  • Releases all locks immediately after transaction abort

• SLOG

  The SLOG is a secondary in-memory log stream that stores log records for nonversioned operations (such as metadata cache invalidation, lock acquisitions, and so on). The SLOG is:

  • Low volume and in-memory
  • Persisted on disk during the checkpoint process
  • Periodically truncated as transactions commit
  • Accelerates redo and undo by processing only nonversioned operations
  • Enables aggressive transaction log truncation by preserving only the required log records

• Cleaner

  The cleaner is the asynchronous process that wakes up periodically and cleans obsolete row versions from PVS.

ADR benefits most workloads, and is particularly beneficial for workloads that have:

• Long-running transactions.
• Active transactions that cause the transaction log to grow significantly.
• Long periods of database unavailability due to long running recovery (such as from unexpected service restart or manual transaction rollback).

ADR isn't supported in databases using database mirroring, an older and deprecated high availability feature.

• Avoid unnecessary long-running transactions. Though ADR speeds up database recovery even with long-running transactions, such transactions can delay version cleanup and increase PVS size.

• Avoid large transactions that include DDL operations. ADR uses the secondary log stream (SLOG) mechanism to track DDL operations used in recovery. SLOG is only used while the transaction is active. SLOG is checkpointed, so avoiding large transactions that use SLOG can help the overall performance. These scenarios can cause the SLOG to take up more space:

  • Many DDLs are executed in one transaction. For example, in one transaction, rapidly creating and dropping temp tables.
  • A table has very large number of partitions/indexes that are modified. For example, a DROP TABLE operation on such table would require a large reservation of SLOG memory, which would delay truncation of the transaction log and delay undo/redo operations. As a workaround, drop the indexes individually and gradually, then drop the table.

  For more information about SLOG, see ADR recovery components.

• Prevent or reduce unnecessary aborted transactions. A high transaction abort rate puts pressure on the PVS cleaner and lowers ADR performance. The aborts might come from a high rate of deadlocks, duplicate keys, constraint violations, query timeouts, or other exceptions. The sys.dm_tran_aborted_transactions DMV shows all aborted transactions on the database engine instance. The nested_abort column indicates that the transaction committed but there are portions that aborted (savepoints or nested transactions) which can also delay the PVS cleanup process.

• Ensure there's sufficient space in the database to account for PVS usage. If the database doesn't have enough room for PVS to grow, ADR might fail to generate versions, causing DML statements to fail.

• When ADR is enabled with write-intensive workloads, transaction log generation rate might increase substantially because row versions written to PVS are logged. This might increase the size of transaction log backups.

• When you use transactional replication, snapshot replication, or change data capture (CDC), the aggressive log truncation behavior of ADR is disabled to allow the log reader to collect changes from the transaction log. Make sure that the transaction log is sufficiently large.

  If using CDC or change feed in Azure SQL Database, you might need to increase your service tier or compute size to ensure that sufficient transaction log space is available for the needs of all your workloads. Similarly, in Azure SQL Managed Instance you might need to increase your instance maximum storage size.

• For SQL Server, when tiered performance storage is available, consider placing the PVS filegroup on higher tier storage. For more information, see Change the PVS filegroup.

• Starting with SQL Server 2022 (16.x), consider enabling multi-threaded PVS cleanup if the single-threaded cleaner performance is insufficient. For more information, see Server configuration: ADR Cleaner Thread Count.

• Starting with SQL Server 2025 (17.x) Preview, if you enable ADR in tempdb, you might need to allocate additional space for PVS data in the tempdb data files. The size of PVS in tempdb can be monitored in the same way as in a user database.

• If you observe issues such as high database space usage by PVS or slow PVS cleanup, see Monitor and troubleshoot accelerated database recovery.

• ADR in tempdb

  Starting with SQL Server 2025 (17.x) Preview, ADR can be enabled in the tempdb database.

  Without ADR, and even with minimal logging used in tempdb, transactions that involve objects such as temporary tables, table variables, or non-temporary tables created in tempdb can be affected by long rollback and high transaction log usage. Running out of tempdb transaction log space can cause significant disruptions and application downtime.

  SQL Server 2025 (17.x) Preview makes the Instantaneous transaction rollback and Aggressive log truncation benefits of ADR available for workloads using transactions in tempdb.

  To enable ADR in tempdb, see Enable ADR.

There are several improvements to address persistent version store (PVS) storage and improve overall scalability. For more information about new features of SQL Server 2022 (16.x), see What's new in SQL Server 2022.

The same improvements are also available in Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (dedicated SQL pool only), and SQL database in Microsoft Fabric Preview.

• User transaction cleanup

  Clean pages that can't be cleaned by the regular process due to lock acquisition failure.

  This feature allows user transactions to run cleanup on pages that couldn't be addressed by the regular cleanup process due to table level lock conflicts. This improvement helps ensure that the ADR cleanup process doesn't fail indefinitely because user workloads can't acquire table level locks.

• Reduce memory footprint for PVS page tracker

  This improvement tracks PVS pages at the extent level, in order to reduce the memory footprint needed to maintain versioned pages.

• PVS cleaner improvements

  PVS cleaner has improved version cleanup efficiency to improve how the database engine tracks and records row versions for aborted transactions. This leads to improvements in memory and storage usage.

• Transaction-level persistent version store (PVS)

  This improvement allows ADR to clean up versions that belong to committed transactions independent of whether there are aborted transactions in the system. With this improvement, PVS pages can be deallocated, even if the cleanup can't complete a successful sweep to trim the aborted transaction map.

  The result of this improvement is reduced PVS growth even if PVS cleanup is slow or fails.

• Multi-threaded version cleanup

  In SQL Server 2019 (15.x), the cleanup process is single threaded within a database engine instance.

  Beginning with SQL Server 2022 (16.x), multi-threaded version cleanup is supported. This allows multiple databases on the same database engine instance to be cleaned in parallel, or a single database to be cleaned faster. For more information, see Server configuration: ADR Cleaner Thread Count.

  A new extended event, tx_mtvc2_sweep_stats, has been added for monitoring of the ADR PVS multi-threaded version cleaner.

• Manage accelerated database recovery
• Monitor and troubleshoot accelerated database recovery

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4