Accelerated Database Recovery for tempdb in SQL Server 2025

Accelerated database recovery was introduced in SQL Server 2019 and provides fast recovery, instantaneous transaction rollback, and aggressive log truncation. A complete overview of how ADR achieves this is documented here.

Now in SQL Server 2025 we can switch on ADR for tempdb! Ok, so the fast recovery part isn’t applicable for tempdb…but the instantaneous transaction rollback and aggressive log truncation 100% are! So let’s have a look at what happens when we switch ADR for tempdb in SQL Server 2025.

Before we run through the test, let’s check how much tempdb log space is in use: –

USE [tempdb];
GO

SELECT database_id, used_log_space_in_percent FROM sys.dm_db_log_space_usage
GO

OK, so not much log in use (as expected).

For this first test, we’ll see what happens without ADR being enabled. To confirm ADR is not enabled: –

SELECT name,is_accelerated_database_recovery_on 
FROM sys.databases
WHERE database_id = 2
GO

Great! So in a new query window, we’ll run: –

USE [tpcc];
GO

SET STATISTICS TIME ON

BEGIN TRANSACTION

SELECT TOP (1000000) * INTO #TempTable
FROM [dbo].[order_line];

UPDATE #TempTable
SET ol_amount = ol_amount + 1;

Just some code to insert data into a temp table and then update…hopefully generating some log for us to analyse.

Btw, the database I’m using here to pull the data from is generated from Anthony Nocentino’s containerised HammerDB instance (really handy, go check it out).

Once that script has been run, now let’s look at the amount of log used in tempdb: –

USE [tempdb];
GO

SELECT database_id, used_log_space_in_percent FROM sys.dm_db_log_space_usage
GO

97%…nearly full. Ok, I admit…I’ve kept the log pretty small on this instance for the purposes of this demo but still…we should see a difference when we re-run this test with ADR enabled.

But first…let’s rollback that transaction: –

ROLLBACK

And the stats from the rollback are: –

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 1187 ms, elapsed time = 1342 ms.

Didn’t take too long but something needed to be rolled back.

Now let’s look what difference there is when we enable accelerated database recovery for tempdb.

To enable ADR on tempdb run: –

ALTER DATABASE [tempdb] SET ACCELERATED_DATABASE_RECOVERY = ON
GO

The good news is that this statement does not require an exclusive lock on tempdb to run. The bad news is that the SQL instance needs to be restarted in order for the change to take effect.

Once SQL has been restarted, confirm that ADR is enabled: –

SELECT name,is_accelerated_database_recovery_on 
FROM sys.databases
WHERE database_id = 2
GO

let’s check the space used in the log again: –

SELECT database_id, used_log_space_in_percent FROM sys.dm_db_log_space_usage
GO

Around 4.4%…now run the query in the second window again: –

USE [tpcc];
GO

SET STATISTICS TIME ON

BEGIN TRANSACTION

SELECT TOP (1000000) * INTO #TempTable
FROM [dbo].[order_line];

UPDATE #TempTable
SET ol_amount = ol_amount + 1

Once that has completed, check how much space has been used in the tempdb transaction log: –

SELECT database_id, used_log_space_in_percent FROM sys.dm_db_log_space_usage
GO

56%…way down from the 97% used with ADR disabled. This is because, when ADR is enabled, the transaction log is “aggressively truncated”…even with active transactions! This is because recovery now relies on the persistent version store (PVS), SLOG, and only part of the transaction log since the last checkpoint. Since there’s no longer a need to retain the log for the whole transaction, log can be truncated aggressively as checkpoints and backups occur.

And now let’s see what happens when we rollback that transaction: –

ROLLBACK

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Near instantaneous rollback!

This is because, instead of scanning the transaction log to roll back changes, ADR enables SQL Server to perform a logical revert using the PVS to instantly undo all versioned operations.

So, pretty handy for any workload with heavy use of temporary objects! However, there are a few things to watch out for with ADR…check out the MS docs for the full list.

Thanks for reading!