Redigera

Dela via

Manage accelerated database recovery

  • Artikel

Applies to: SQL Server 2019 (15.x) and later versions

This article teaches you to enable and disable accelerated database recovery (ADR) with Transact-SQL (T-SQL) in SQL Server 2019 (15.x) and later versions, as well as how to change the persistent version store (PVS) filegroup used by ADR.

Note

In Azure SQL Database, Azure SQL Managed Instance, and SQL database in Microsoft Fabric, accelerated database recovery (ADR) is always enabled. If you observe issues, such as high storage usage by PVS or slow ADR cleanup, see Troubleshoot accelerated database recovery or contact Azure support.

Who should consider accelerated database recovery

Many customers find accelerated database recovery (ADR) a valuable technology to improve database recovery time.

If your database workloads frequently encounter the following scenarios, you might benefit from ADR:

  • Long running transactions that can't be avoided. For example, in cases where long-running transactions are at risk of being rolled back, ADR can help.
  • Active transactions that cause the transaction log to grow significantly.
  • Long-running database recovery that impacts the availability of the database (for example, after an unexpected SQL Server restart or manual transaction rollback).

ADR isn't recommended for the following scenarios:

  • Databases using database mirroring aren't supported.
  • If your application uses a high volume of single-row modifications in individual transactions, your workload might not be optimal for ADR. Consider batching modifications in multi-row statements where possible, and avoid a high volume of small DML transactions.

Enable ADR

ADR is off by default and available starting with SQL Server 2019 (15.x).

Use the following Transact-SQL (T-SQL) command to enable ADR:

ALTER DATABASE [<db_name>] SET ACCELERATED_DATABASE_RECOVERY = ON;

An exclusive database lock is necessary to enable or disable ADR. That means the ALTER DATABASE command is blocked until all active sessions are gone, and that any new sessions wait behind the ALTER DATABASE command. If it's important to complete the operation and remove the lock, you can use the termination clause, WITH ROLLBACK [IMMEDIATE | AFTER {number} SECONDS | NO_WAIT] to abort any active sessions in the database. For more information, see ALTER DATABASE SET options.

Disable ADR

Use the following T-SQL command to disable ADR:

ALTER DATABASE [<db_name>] SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO

Even after ADR is disabled, there might be versions stored in PVS that the system still needs for logical revert until all active transactions complete.

Change the PVS filegroup

By default, the persistent version store (PVS) data is on the PRIMARY filegroup. You can move PVS to a different filegroup if necessary. For example, it might require more space or faster storage.

To change the location of the PVS to a different filegroup, follow these steps:

  1. Create the filegroup for PVS and add at least one data file to this filegroup. For example:

    ALTER DATABASE [<db_name>] ADD FILEGROUP [VersionStoreFG];
GO

ALTER DATABASE [<db_name>]
ADD FILE
(
   NAME = N'VersionStoreFG',
   FILENAME = N'E:\DATA\VersionStore.ndf',
   SIZE = 8192 MB,
   FILEGROWTH = 64 MB
)
TO FILEGROUP [VersionStoreFG];

  2. Disable ADR with the following T-SQL command:

    ALTER DATABASE [<db_name>] SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO

  3. Wait until all versions stored in PVS are removed.

    To enable ADR using a new PVS location, first ensure all version information has been purged from the previous PVS location. You can force the cleanup to happen with the sys.sp_persistent_version_cleanup stored procedure:

    EXEC sys.sp_persistent_version_cleanup [<db_name>];

    The sys.sp_persistent_version_cleanup stored procedure is synchronous, which means it won't complete until all version information is cleaned up from the current PVS. Once it completes, you can verify version information is removed by querying sys.dm_tran_persistent_version_store_stats and examining the value of persistent_version_store_size_kb, such as the following sample:

    SELECT DB_NAME(database_id),
       persistent_version_store_size_kb
FROM sys.dm_tran_persistent_version_store_stats
WHERE database_id = [MyDatabaseID];

    When the value of persistent_version_store_size_kb is 0, you can re-enable the ADR feature, with the PVS in the new filegroup.

  4. Enable ADR and specify the new PVS location with the following T-SQL command:

    ALTER DATABASE [<db_name>] SET ACCELERATED_DATABASE_RECOVERY = ON
(PERSISTENT_VERSION_STORE_FILEGROUP = [VersionStoreFG]);

Monitor the size of the PVS

Once you enable ADR on a database, monitor the size of the persistent version store (PVS) and PVS cleanup performance. You can monitor PVS health using the methods found in Troubleshoot accelerated database recovery.

If you have a workload with a high volume of DML statements (INSERT, UPDATE, DELETE, MERGE), such as high-volume OLTP, it might require a period of rest/recovery for the PVS cleanup process to reclaim space. Typically, business operation cycles allow for this time, but in some scenarios you might want to initiate the PVS cleanup process manually to take advantage of application activity patterns.

  • To activate the PVS cleanup process manually between workloads or during maintenance windows, use the sys.sp_persistent_version_cleanup stored procedure.

  • If the PVS cleanup process is running for a long period time, you might find that the count of aborted transactions grows, which also causes the PVS size to increase. Use the sys.dm_tran_aborted_transactions DMV to report the aborted transaction count, and use sys.dm_tran_persistent_version_store_stats to report the cleanup start/end times along with the PVS size.

  • Workloads featuring long-running queries using SNAPSHOT isolation, or READ COMMITTED isolation when the READ_COMMITTED_SNAPSHOT (RCSI) database option is enabled might delay PVS cleanup for all databases on a database engine instance, causing the PVS size to grow. For more information, see the section on long active snapshot scans in Troubleshoot accelerated database recovery.

Related content