Two weeks ago I covered the Resource Governor changes in SQL Server 2025 — specifically, capping how much tempdb data space a workload group can consume. That was the data-file side. For the log side, SQL Server 2025 now lets you enable Accelerated Database Recovery (ADR) on tempdb. Enable it and cancelled queries stop grinding, the tempdb log stops bloating, and recovery gets faster. Sounds like an easy yes — but you've got to read the fine print.
What ADR Does — and Why It Matters for TempDB
ADR has been around for user databases since SQL Server 2019. The core idea: instead of a long, painful undo phase when a query is cancelled or a transaction rolls back, SQL Server maintains a Persistent Version Store (PVS) that lets it undo work almost instantly. No more watching Cancelling query... while the tempdb log blows. Rollback happens instantly regardless of how much work was in flight.
SQL Server 2025 extended the ADR support to the tempdb system database. The tempdb log has always been its own special kind of problem — runaway transactions, ETL jobs that hold space long after they should have cleaned up, startup times dragged out by whatever was open when things went sideways. ADR addresses all of it. Instant rollback, aggressive log truncation, predictable recovery. The concept is solid, but the execution has some rough edges you need to know about before touching production.
How to Enable It
ADR is available in both Standard and Enterprise editions, but disabled by default on tempdb, and must be configured:
ALTER DATABASE TempDB SET ACCELERATED_DATABASE_RECOVERY = ON; GO
To check whether it is currently enabled:
SELECT name, is_accelerated_database_recovery_on FROM sys.databases WHERE name = 'tempdb';
A restart is required to enable or disable ADR on tempdb. Not optional. TempDB is rebuilt from scratch on every service start, so ADR infrastructure has to be there at creation time. The documentation is clear on this. Plan a maintenance window.
What Happens to Tempdb Storage
When ADR is on, tempdb runs two independent version stores simultaneously: the traditional version store for row versions from user databases using RCSI or snapshot isolation, and the new PVS for tempdb transactions. Both live in the tempdb data files, and Microsoft is upfront that tempdb data space requirements will increase. If your tempdb drive is already tight, figure out the space before you enable this, not after. PVS size in tempdb can be monitored the same way as in any user database.
The Known Issues — Not Exactly Front and Center
Microsoft has documented several known issues with ADR on tempdb. What's notable is where that documentation lives — not on the main ADR feature page, not on the tempdb page, but tucked into the ADR troubleshooting article. A page many people never open before enabling a feature - or after. Here's what's in there:
Performance Degradation (Latch Contention)
In environments with high-frequency create/drop of temporary tables, enabling ADR on tempdb can cause severe throughput degradation. The culprit is latch contention on the sys.sysobjvalues internal system table as multiple processes compete for PVS access simultaneously. High-concurrency OLTP with per-session temp tables, stored procedures spinning up temp tables in loops, apps firing create/drop in rapid succession — all at risk. Heavy ETL that creates a few large temp tables and holds them is generally fine. It's the volume and velocity of DDL events that triggers the contention, not the data size. Under investigation, no fix date. See Monitor and Troubleshoot ADR — Microsoft Learn.
Increased Transaction Log Generation
ADR logs every row version written to the PVS. In practice, that means enabling it on tempdb can substantially increase transaction log generation — which is a bit ironic given that log reduction is one of the reasons you'd even turn this on. See Accelerated Database Recovery — Microsoft Learn.
Long-Running Transaction Risks
ADR makes rollbacks fast, but it doesn't make the PVS self-managing. Long-running active transactions block PVS cleanup, which means the PVS grows — and keeps growing — until those transactions close. Add SNAPSHOT or RCSI isolation to the mix and the problem compounds. Left unchecked, a long-running transaction can still fill tempdb right through the PVS. See Monitor and Troubleshoot ADR — Microsoft Learn.
When It's Worth Enabling
If your environment has real tempdb log pressure — regular blowouts on ETL runs, rollbacks that drag on long after a query is cancelled, startup times that vary wildly depending on what was running when things went down — this feature is aimed directly at you. It works. The concept is proven on user databases and the mechanics are the same here.
Before you enable it on TempDB:
• Test against real workload patterns, not a sanitized demo environment.
• Avoid large transactions that include heavy DDL operations.
• Pre-size tempdb data files to account for PVS growth before flipping the switch.
• Monitor PVS size and cleanup after enabling — don't assume it's managing itself.
• If your workload involves heavy temp table churn at high concurrency, I'd hold off until the latch contention issue is resolved.
The Bottom Line
The Resource Governor caps and ADR on tempdb together give you real control over tempdb growth problems for the first time. They're complementary and each must be configured separately. The Resource Governor side is production-ready today. ADR on tempdb is promising but not ready yet. Microsoft is still working through the known issues. Keep an eye on the release notes and known issues, and when that latch contention fix ships, this becomes a much easier recommendation.
More to Read
TempDB Database — Microsoft Learn
Accelerated Database Recovery — Microsoft Learn
Monitor and Troubleshoot ADR — Microsoft Learn
Accelerated Database Recovery in SQL Server 2025 — Straight Path SQL
Resource Governor in SQL Server 2025 — sqlfingers.com
No comments:
Post a Comment