Instantaneous Transaction Rollback with SQL 2019

If you’ve read about the Accelerated Database Recovery feature in SQL Server 2019 you could be forgiven for thinking it’s just about speeding up database recovery time in case of a server failure.

In fact, enabling it also means that where you have a long running transaction that fails or is cancelled the rollback is almost instantaneous. This is great news for DBAs who have to sometimes kill a long-running blocking transaction but worry that it may take a long time to rollback – continuing to block all that time.

This is achieved by the fact that Accelerated Database Recovery maintains a version store in the database, and where a row is updated, the old version of the row is kept until after the transaction is complete. That makes it quick and easy to revert to the old version in case of failure.

Let’s look at a quick example.

I have a table with about 10 million rows – all containing the same text value:

CREATE DATABASE TestADR;
USE TestADR;
CREATE TABLE dbo.TestADR(Id int IDENTITY, SomeText varchar(50));
INSERT INTO dbo.TestADR (SomeText)
SELECT TOP 10000000 'FrangipanDeluxe' 
FROM sys.objects a, sys.objects b, sys.objects c, sys.objects d;

I update all the rows in the table to a new value:

UPDATE dbo.TestADR SET SomeText = 'FrangipanDeluxA';

This took about a minute.

I then execute a query to change them back and cancel the query in SSMS after about 30 seconds.

UPDATE dbo.TestADR SET SomeText = 'FrangipanDeluxe';

It took about 30 seconds more to cancel – which is SQL rolling back the changes.

Then I enabled Accelerated Database Recovery, you do this at the database level:

ALTER  DATABASE TestADR 
SET ACCELERATED_DATABASE_RECOVERY = ON;

Now I re-run that last update, again cancelling after 30 seconds.

This time the cancel was instantaneous, it took SQL no noticeable amount of time to roll back the changes.

This is great but we’ll probably want to be careful before we enable it on all our databases – when we get them onto SQL 2019 anyway. There will be an additional overhead in managing the version store and that could have an impact in terms of time taken to complete write queries, as well as storage requirements.

Still, it seems like a good feature  – something to look forward to playing with more.