Introduction
Accelerated database recovery (ADR) is an MSSQL database engine feature that improves database availability in three ways:
- Consistently fast database recovery
- Instant transaction rollback
- Aggressive log truncation
ADR is enabled in Azure SQL Database and Azure SQL Managed Instance for all user databases, reducing downtime during planned and unplanned failovers, and avoiding application outages due to long transaction rollbacks. Customers running SQL Server 2019 and SQL Server 2022 in Azure VMs or on-premises can enable ADR to enjoy the same benefits.
Today, we are pleased to announce that in Azure SQL Database, ADR is also enabled in the tempdb database.
Update March 12, 2025: After ADR was enabled in tempdb in the second half of February 2025, we identified a problem that might affect workload performance in certain cases when this feature is enabled. To avoid affecting customer workloads, on February 28, 2025, we temporarily disabled ADR in tempdb for Azure SQL Database. We apologize for the inconvenience and are working on a fix.
Why does this matter?
Tempdb doesn’t need to go through recovery because it’s recreated on every startup. Transactions in tempdb are minimally logged. Because of this, you might think that the benefits of ADR aren’t applicable to tempdb.
However, it is still possible for a long-running transaction that involves objects in tempdb (temporary tables, table variables, etc.) to consume a lot of transaction log space.
Without ADR, a rollback of such a transaction can be time-consuming, and if the transaction is large enough, tempdb can run out of the available log space. In either case, this can lead to long downtime and outages. These problems can particularly affect elastic pools in Azure SQL Database, where tempdb is a shared resource among all databases in the pool.
Improvement
The following chart shows the reduction of tempdb transaction log size due to aggressive log truncation with ADR enabled. The X axis is the 90th percentile of the maximum transaction log size for a representatively large subset of databases in Azure SQL Database.
- The maximum tempdb transaction log size with ADR enabled (the short green bar) is reduced by more than 9 times, indicating a drastic reduction in the amount of generated log.
- Transaction rollback in tempdb is now instantaneous.
Conclusion
With ADR enabled in tempdb, long rollbacks and running out of log space in tempdb are a thing of the past in Azure SQL Database. In the future, customers can expect the same improvement to become available in Azure SQL Managed Instance and in a future version of SQL Server.
We look forward to sharing news about new MSSQL database engine capabilities in the pipeline as they become available.
Microsoft