Accelerated Database Recovery

SQL Server 2019 introduced a new database engine feature called Accelerated Database Recovery. The primary purpose it serves is the faster database recovery be it from a long running transaction, recovery from a crash or the time it takes to failover due to active transactions. It achieves all this by having a change in the way the transactions are handled in SQL Server. We all know that the Transaction Log plays the critical role in maintaining the ACID properties of transactions. Before, we dig down how ADR manages it, let’s see what is the current recovery process in SQL Server –

A recovery process consists of three different phases/steps. In simple words, it starts from analyzing to determine the state of database when it got crashed/failed over or a long running transaction is stopped. Then the transactions which were committed into log but not written to the database are “Redone” and the transactions which were rollbacked are “Undone”.

And all this is done by reading the transaction log and remember reading the transaction log is a sequential process i.e. no random access so it takes quite a good time to complete all these steps/phases. ADR fast tracks this by introducing some additional components in between like PVS (Persistent Version Store) and sLog.

PVS contains the previous version of rows modified by the transactions and sLog is the in-memory stream which holds the activities which have not been written to PVS. With ADR, the recovery process look like –

Analysis process remains the same as the database engine still needs to determin the state of database but Redo and Undo operations are quite faster as rather than going back to transaction log, these operations are performed from secondary log i.e. sLog which contains only the relevant information needed to rollback/commit the changes.

With ADR enabled, data file size of a database can be longer than the database with ADR not enabled as it maintains a local version store i.e. PVS on the data file. However, since ADR allows aggressive transaction log truncation so it may not grow too much.

ADR is disabled by default in SQL Server 2019 but enabled on Azure SQL databases including Managed Instances.

You can check if ADR is enabled or not as –

SELECT compatibility_level, is_accelerated_database_recovery_on, * FROM sys.databases

Once this is enabled, flag would be 1. You can enable this as –

ALTER DATABASE ADRTEST SET ACCELERATED_DATABASE_RECOVERY = ON;

Caveat: This may take quite longer when enabled on a database which is already huge. I am still figuring out the cause and a possible remediation for this if it is consistent or if there are any recommendations for it. So, until then it remains a caveat.

Further, you can check the size of PVS as –

SELECT
    persistent_version_store_size_kb
FROM
sys.dm_tran_persistent_version_store_stats
WHERE
    database_id = DB_ID('ADRTest')

Persistent version store can be cleaned up by executing the query –

EXECUTE sys.sp_persistent_version_cleanup;
-- OR directly from master database
EXECUTE master.sys.sp_persistent_version_cleanup 'ADRTEST';

To test, how quickly ADR rollbacks a long running transaction, you can run the following code once with ADR disabled and then after enabling it –

DROP TABLE IF EXISTS dbo.ADRTest
GO
CREATE TABLE ADRTest
(
	ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
GO

BEGIN TRANSACTION

WHILE (1=1)
BEGIN
	INSERT Into ADRTest DEFAULT VALUES
END

ROLLBACK -- Run this after few seconds when previous query already had inserted good volume of data

In short, ADR achieves this by maintaining a secondary log (sLog) and a version store of rows (PVS) in the table which is persisted along with the data itself in data file.