An old problem
Since the early days of SQL Server, DBAs had to contend with a common problem – running out of space in the tempdb database.
It has always struck me as odd that all I need to cause an outage on an SQL Server instance is access to the server where I can create a temp table that fills up tempdb, and there is no permission to stop me.
- Erland Sommarskog (website), an independent SQL Server consultant and a Data Platform MVP
Because tempdb is used for a multitude of purposes, the problem can occur without any explicit user action such as creating a temporary table. For example, executing a reporting query that spills data to tempdb and fills it up can cause an outage for all workloads using that SQL Server instance.
Over the years, many DBAs developed custom solutions that monitor tempdb space and take action, for example kill sessions that consume a large amount of tempdb space. But that comes with extra effort and complexity.
I have spent more hours in my career than I can count building solutions to manage TempDB space. Even with immense time and effort, there were still quirks and caveats that came up that created challenges - especially in multi-tenant environments with lots of databases and the noisy-neighbor problem.
- Edward Pollack (LinkedIn), Data Architect at Transfinder and a Data Platform MVP
A new solution in the SQL Server engine
SQL Server 2025 brings a new solution for this old problem, built directly into the database engine. Starting with the CTP 2.0 release, you can use resource governor, a feature available since SQL Server 2008, to enforce limits on tempdb space consumption.
We rely on Resource Governor to isolate workloads on our SQL Server instances by controlling CPU and memory usage. It helps us ensure that the core of our trading systems remains stable and runs with predictable performance, even when other parts of the systems share the same servers.
- Ola Hallengren (website), Chief Data Platforms Engineer at Saxo Bank and a Data Platform MVP
Similarly, if you have multiple workloads running on your server, each workload can have its own tempdb limit, lower than the maximum available tempdb space. This way, even if one workload hits its limit, other workloads continue running.
Here's an example that limits the total tempdb space consumption by queries in the default workload group to 17 GB, using just two T-SQL statements:
ALTER WORKLOAD GROUP [default] WITH (GROUP_MAX_TEMPDB_DATA_MB = 17408);
ALTER RESOURCE GOVERNOR RECONFIGURE;The default group is used for all queries that aren’t classified into another workload group. You can create workload groups for specific applications, users, etc. and set limits for each group.
When a query attempts to increase tempdb space consumption beyond the workload group limit, it is aborted with error 1138, severity 17, Could not allocate a new page for database 'tempdb' because that would exceed the limit set for workload group 'workload-group-name'.
All other queries on the server continue to execute.
Setting the limits
You might be asking, “How do I know the right limits for the different workloads on my servers?”
No need to guess. Tempdb space usage is tracked for each workload group at all times and reported in the sys.dm_resource_governor_workload_groups DMV. Usage is tracked even if no limits are set for the workload groups. You can establish representative usage patterns for each workload over time, then set the right limits. You can reconfigure the limits over time if workload patterns change.
For example, the following query lets you see the current tempdb space usage, peak usage, and the number of times queries were aborted because they would otherwise exceed the limit per workload group:
SELECT group_id,
name,
tempdb_data_space_kb,
peak_tempdb_data_space_kb,
total_tempdb_data_limit_violation_count
FROM sys.dm_resource_governor_workload_groups;
Peak usage and the number of query aborts (limit violations) are tracked since server restart. You can reset these and other resource governor statistics to restart tracking at any time and without restarting the server by executing
ALTER RESOURCE GOVERNOR RESET STATISTICS;What about the transaction log?
The limits you set for each workload group apply to space in the tempdb data files. But what about the tempdb transaction log? Couldn’t a large transaction fill up the log and cause an outage?
This is where another feature in SQL Server 2025 comes in. You can now enable accelerated database recovery (ADR) in tempdb to get the benefit of aggressive log truncation, and drastically reduce the possibility of running out of log space in tempdb. For more information, see ADR improvements in SQL Server 2025.
Learn more
For more information about tempdb space resource governance, including examples, best practices, and the details of how it works, see Tempdb space resource governance in documentation.
If you haven’t used resource governor in SQL Server before, here’s a good starting point: Tutorial: Resource governor configuration examples and best practices.
Conclusion
SQL Server 2025 brings a new, built-in solution for the age-old problem of tempdb space management. You can now use resource governor to set limits on tempdb usage and avoid server-wide outages because tempdb ran out of space.
We are looking forward to your feedback on this and other SQL Server features during the public preview of SQL Server 2025 and beyond. You can leave comments on this blog post, email us at sql-rg-feedback@microsoft.com, or leave feedback at https://aka.ms/sqlfeedback.
Microsoft