Rediger

Del via

Manage accelerated database recovery

  • Artikkel

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 Monitor and 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).

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, and assuming ADR is disabled, 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. For example:

    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 ADR and place PVS on 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 in a database, monitor the size of the persistent version store (PVS) and PVS cleanup performance. For more information, see Monitor and troubleshoot accelerated database recovery.

Related content