Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted October 14, 2019

Accelerating Database Recovery with SQL Server 2019

By Greg Larsen

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: 


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.

» See All Articles by Columnist Gregory A. Larsen

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM