Know accelerated database recovery feature in SQL server

Read time 4 minutes

In this blog, we will be discussing about the Accelerated Database Recovery feature of SQL Server and how is it an advantage for users waiting for so long for completing the running recovery transactions.

There are many scenarios when an SQL Server needs to run the automatic database recovery like when the administrator is running a large DML query update and then due to unfortunate reasons (deadlock, performance issues), this query needs to be terminated in the mid-way. Then this query will go into the Rollback state which is a long-recovery procedure and then user has to wait for its completion.

Let us take one more example, if the user starts inserting large numbers into any table and then the SQL Server system has got crashed. On restarting the system, the user needs to restart the SQL Services, the services will show online but still in the recovery mode for long time for which the users need to wait again.

To overcome this long-waiting process for SQL database recovery, a new feature Accelerated Database Recovery is introduced. Let us know about this feature.

Accelerated database recovery (ADR) – introduction

ADR is a new feature introduced lately in the SQL database engine with improved database recovery process which helps in delivering the quicker recovery of database along with running transactions. It is available for single and pooled databases in Azure SQL database and all for all databases in Azure SQL Data Warehouse. ADR includes the following components:

Persistent Version Store (PVS) Mechanism –
This mechanism allows persisting the row versions in the database only rather keeping it in the old tempdb store. Through PVS,
- More Readable Secondaries
- Resource isolation is enabled
Logical Revert Process –
It is an asynchronous procedure which offers quick rollback/undo operations. Through Logical Revert,
- Rollback for all user transactions via Persistence Version Store
- Immediate releasing of all locks once transaction is aborted
- Storing track for all the aborted transactions
sLog Service –
It is a low-volume, secondary in-memory log stream for storing log records for non-versioned operations. Through sLog,
- Periodic truncation of sLog
- Accelerated Redo and Undo processes via non-versioned operations only
- Faster log transaction with only required log records
Page versions Cleaner –
As the name suggests, it cleans unrequired page versions, works periodically and in non-synchronized manner.

So, we now know what Accelerated Database Recovery feature is and about its components. Let us discuss more on its working process.

Accelerated database recovery (ADR) – working process

ADR includes a redesigned SQL database engine recovery process. It helps in attaining faster recovery by versioning all the physical database changes while undoing limited logical operations. And the transactions active while the system got crashed is considered as aborted which would let concurrent queries avoid any transactions generated through these transactions.

The 3 improved phases in Accelerated Database Recovery are as follows:

Phase 1 – Analysis

This phase has improvements like

Introduction of reconstructing sLog
Storing log records for non-versioned operations

Phase 2 – Redo

This phase is improved by breaking it into 2 phases.

In the first phase, Redo is performed through sLog which starts from the last uncommitted transaction to the latest checkpoint.
In the second phase, Redo is performed through transaction log which starts from the latest checkpoint.
First phase is faster operation than the second phase as few records are needed to be processed in sLog.

Phase 3 – Undo

This Undo phase with ADR is performed very quickly.

Using sLog to undo non-versioned operations.
Row-level version -based Undo is performed using Persisted Version Store with Logic Revert.

So, this Accelerated Database Recovery process is a boon for the SQL users stuck in the long running transactions and are experiencing long database availability period. Apart from this, if the SQL database is severely corrupted due to any unfortunate user and not accessible from any way, you have Kernel for SQL Database software along with it. This SQL Recovery software handles all errors and corruption issues quite smoothly and recovers database of all SQL versions in a very short time.

Free Download 100% Secure

How to Backup and Restore a SQL Server Database? Step-By-Step Tutorial

How to repair a corrupt SQL Database?

Steps to restore SQL database on existing database in SQL server