Have you ever cancelled a large transaction, only to find it doesn’t cancel immediately? Instead of cancelling immediately you must wait for a period of time before the transaction is cancelled. The reason it takes so long to cancel a large transaction is because SQL Server has to read the transaction log to restore the data pages back to the images prior to the long-running transaction. Restoring all these old images from the transaction log takes the time and is why your transaction doesn’t terminate instantly.
With the introduction of SQL Server 2019, the SQL Server team has implemented a database recovery option called “Accelerated Database Recover”. This new database option can be turned on by using the following command:
ALTER DATABASE MyDB SET ACCELERATED_DATABASE_RECOVERY = ON;
At a high level when this new feature is turned on for a database the SQL Server engine stores old and new versions of updated rows in the database in a Persisted Version Store (PVS), instead of storing these row versions in tempdb. Additionally, all non-versioned operations are stored in a new log, called the sLog. By doing this SQL Server is able to undo a transaction by reading the old row version from the database asynchronously and use the sLog to roll back the non-versioned operations. Because the old row version is stored in the PVS the database engine is able to almost instantaneous roll back the versioned updates.
This new database feature is targeted databases that have:
- Workloads with long running transaction
- Transactions that cause the transaction log to grow significantly
- Database that are unavailable for long period of time while the database is being rolled back due to a cancelled transaction, or Database recovery takes a long period after a manual rollback, or SQL Server restart.
If you are looking to speed up the rollback process and can live with the additional database space needed for the PVS, then you might want to turn on the “Accelerate Database Recovery” option.
