Capping tempdb with Resource Governor (SQL Server 2025)

In one line: SQL Server 2025’s Resource Governor can cap how much tempdb a workload group uses. Temporary tables, table variables and spills count; the version store doesn’t. The query that goes over fails with error 1138, and a percentage limit is ignored on a tempdb that autogrows.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/tempdb-space-governance on GitHub.

What you’ll see

  • Error 1138: “Could not allocate a new page for database ‘tempdb’ because that would exceed the limit set for workload group ‘default'”.
  • A query that fails although it creates only 15 MB, because other work in the same group already holds the rest.
  • A rolled-back transaction: XACT_STATE() is -1 after error 1138.
  • A limit that doesn’t apply: after ALTER WORKLOAD GROUP without ALTER RESOURCE GOVERNOR RECONFIGURE, or a percentage limit on autogrowing files, with the message “GROUP_MAX_TEMPDB_DATA_PERCENT is not in effect because tempdb configuration requirements aren’t met.”

Why it happens

GROUP_MAX_TEMPDB_DATA_MB caps the tempdb data space all the sessions in a workload group hold between them. It counts temporary tables, table variables and the work tables that sorts and hashes spill into. It doesn’t count the version store, which belongs to no session.

A percentage limit is a share of tempdb’s maximum size. If the files autogrow with no MAXSIZE, there’s no maximum, so the limit isn’t enforced.

What to do

  • Put the workload you want to cap in its own workload group, with a classifier function: a cap on default applies to nearly everyone.
  • Use GROUP_MAX_TEMPDB_DATA_MB, or give tempdb fixed-size files before relying on a percentage.
  • Run ALTER RESOURCE GOVERNOR RECONFIGURE and check is_reconfiguration_pending afterwards.
  • Watch total_tempdb_data_limit_violation_count in sys.dm_resource_governor_workload_groups.
  • Make the capped application handle error 1138: its transaction is rolled back.

What doesn’t help

  • Expecting it to protect tempdb from the version store. A long transaction under RCSI or snapshot isolation can still fill tempdb.
  • A percentage limit on default tempdb settings.

Check your server

-- SQL Server 2025+: tempdb limits per workload group, what each group holds
-- now, and how often the limit has been hit.
SELECT g.name,
       g.group_max_tempdb_data_mb,
       g.group_max_tempdb_data_percent,
       s.tempdb_data_space_kb,
       s.peak_tempdb_data_space_kb,
       s.total_tempdb_data_limit_violation_count
FROM sys.resource_governor_workload_groups AS g
JOIN sys.dm_resource_governor_workload_groups AS s ON s.group_id = g.group_id;

-- Can a percentage limit apply? Not while any data file autogrows without
-- a MAXSIZE.
SELECT name, size / 128 AS size_mb, growth, max_size
FROM tempdb.sys.database_files
WHERE type = 0;

See it happen

The tempdb-space-governance demo puts a 20 MB limit on the default workload group, then tests temporary tables, table variables, a sort spill, the version store, a transaction and a percentage limit. It changes Resource Governor for the whole instance while it runs, then puts it back: use a scratch instance. On 2022 and earlier it reports SKIPPED.

Related: tempdb and #temp tables · RCSI and snapshot isolation · memory grants

Measured on SQL Server 2025 RTM-CU10 (17.0.5035.5), Linux container. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights