Controlling TempDB Resources with Resource Governor in SQL Server 2025

Introduction

Every SQL Server instance relies on tempdb as its universal scratch pad. When one poorly-written report or maintenance job floods that database with temporary objects, the entire server can stall or even go offline. SQL Server 2025 finally gives database administrators a precise brake pedal: TempDB Space Resource Governance, an extension to Resource Governor that lets you cap how much tempdb space each workload can consume.

How the new governance works

Resource Governor already funnels incoming sessions into workload groups and can throttle their CPU, memory, and I/O. SQL Server 2025 adds two new settings that you can attach to any workload group:

• GROUP_MAX_TEMPDB_DATA_MB – a hard limit expressed in megabytes.
• GROUP_MAX_TEMPDB_DATA_PERCENT – a limit expressed as a percentage of the maximum size of tempdb.

When a statement would push the group past its quota, SQL Server cancels the request with error 1138 and increments a violation counter. The failure happens before the data-file grows, so other workloads remain unaffected. Internally, the engine tracks every 8-KB allocation tied to a session—temporary tables, hash and sort spill files, version-store pages, and more. Space used by the common version store is not governed because those pages can be shared by multiple groups.

Why you should enable it

• Contain runaway queries – A single ad-hoc query can no longer fill the drive and crash the instance.
• Fair multi-tenancy – Allocate separate quotas for each application, tenant, or business unit on a shared server.
• Reliable batch windows – Large ETL or index-rebuild jobs get their own ceiling, protecting interactive workloads.
• Proactive alerting – Limit violations are surfaced instantly in dynamic-management views, so monitoring tools can raise alerts before disk-space alarms fire.

Practical scenarios

• SaaS platforms hosting dozens of customer databases on one VM.
• Banking systems where overnight batch cycles share a server with daytime OLTP.
• Development and QA boxes where testers often run “SELECT * INTO #huge_table”.
• Analytics workloads that spill to tempdb during large joins or sorts.

Quick configuration example

The script below caps the default workload at 20 GB and places an ETL tool in its own workload group limited to 15% of tempdb. Adjust the classifier logic to match your environment.

-- Create a workload group for ETL sessions and cap it at 15% of tempdb
CREATE WORKLOAD GROUP etl_group
WITH (GROUP_MAX_TEMPDB_DATA_PERCENT = 15);

-- Classify sessions whose application name is 'DataStage' into that group
USE master;
GO
CREATE OR ALTER FUNCTION dbo.rg_classifier()
RETURNS sysname WITH SCHEMABINDING AS
BEGIN
    RETURN (CASE WHEN APP_NAME() = 'DataStage'
                 THEN 'etl_group'
                 ELSE 'default'
            END);
END;
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);

-- Cap all remaining sessions (default group) at 20 GB
ALTER WORKLOAD GROUP default
    WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480);

-- Turn the new rules on
ALTER RESOURCE GOVERNOR RECONFIGURE;

After deployment you can monitor usage and any limit breaches in real time:

SELECT name,
       group_max_tempdb_data_mb,
       group_max_tempdb_data_percent,
       tempdb_data_space_kb,
       total_tempdb_data_limit_violation_count
FROM   sys.dm_resource_governor_workload_groups;

Operational tips

• Measure before you restrict. Leave the limits at NULL for a week and record each group’s peak usage to establish a safe ceiling.
• Assign everything. Internal engine tasks run in the default group, so give that group enough headroom or explicitly classify those sessions elsewhere.
• Fix file sizes if you use percentages. GROUP_MAX_TEMPDB_DATA_PERCENT relies on tempdb having a firm MAXSIZE or disabled autogrowth; otherwise the percentage moves as the files grow.

Conclusion

TempDB Space Resource Governance in SQL Server 2025 brings long-awaited protection against runaway tempdb usage. By defining just a few workload-group limits you can keep critical workloads responsive, prevent emergency disk-full events, and make multi-tenant instances predictable. If you manage any busy or shared SQL Server, add this feature to your standard build script on day one.

Further Reading from Microsoft documentation:

• TempDB Space Resource Governance
• TempDB Space Resource Governance Walkthrough