Managing TempDB Growth in SQL Server 2025 Using Resource Governor

Haripriya Naidu

Jul 23, 2025

SQL Server 2025 introduces two new options to help manage the growth of TempDB:

Resource Governor on TempDB helps manage TempDB data file usage.
Accelerated Database Recovery (ADR) on TempDB helps manage the log file usage - You should really wait on getting this enabled since there is performance degradation with ADR on TempDB due to contention on system objects.

In this article, we’ll focus on how to control TempDB datafile growth using Resource Governor (RG).

RG is disabled by default and available only on Enterprise edition.
Until SQL Server 2022, RG could only manage user databases.
Starting with SQL Server 2025, RG can now manage TempDB as well.

Demo

Demo Setup
Version: SQL Server 2025 CTP 2.1
Database: DBATest

Check if RG is enabled.
Result: 0 means disabled.

SELECT is_enabled 
FROM sys.resource_governor_configuration;

Enable resource governor
```
 ALTER RESOURCE GOVERNOR RECONFIGURE;
```
No restart is needed.
Verify RG is enabled.
Result: 1 means enabled.
There are a set of things that needs to be configured step by step
1. Create a resource pool
2. Set a threshold limit for the default workload group or
3. Create a user-defined workload group under that resource pool
4. Set a threshold limit for the user-defined workload group
5. Classify applications, usernames, or hostnames to be directed to either the default or the user-defined workload group
Configuration

Create Resource Pool

	USE master;
	CREATE RESOURCE POOL RGDemo_TempDB
	WITH
	(   MAX_CPU_PERCENT = 75,
         MAX_MEMORY_PERCENT = 75);

Verify Resource pool created.
Row 3 - RGDemo_TempDB is the pool we just created.
```
SELECT * FROM sys.resource_governor_resource_pool
```

In SSMS Object explorer, you will see the new resource pool under Resource Governor
Set TempDB space limits for the default workload group.
For demo purpose, I’m setting max limit of default workload group to a smaller size 1 MB, that means any user/session using more than 1 MB of TempDB space will be stopped. Since no classifier functions are created yet, all sessions using TempDB will fall under default workload group limits.
Run reconfigure after setting the limit to get the configuration in effect.
Alternatively, GROUP_MAX_TEMPDB_DATA_PERCENT can also be set instead of data space in MB.
Let’s test by dumping data from sys.sysobjects into a temp table.
If the operation needs more than 1 MB, then the operation will fail.
```
SELECT * INTO #temptable FROM sys.sysobjects;
```
Could not allocate a new page for database 'tempdb' because that would exceed the limit set for workload group 'default', group_id 2.
Since the space needed was more than the limit 1 MB, the operation failed.

Check how many times RG aborted transactions due to limit.
In this result, you can see that the number of times limit exceeded was 2 and that means RG had to step in twice to abort the transactions. (I had run the same dump again)

SELECT dm_resource_governor_workload_groups.group_id,
       dm_resource_governor_workload_groups.[name],
       resource_governor_workload_groups.group_max_tempdb_data_mb,
       dm_resource_governor_workload_groups.total_tempdb_data_limit_violation_count AS [No of times data space limit was exceeded],
       dm_resource_governor_workload_groups.tempdb_data_space_kb AS [Current TempDB data space limit],
       dm_resource_governor_workload_groups.peak_tempdb_data_space_kb AS [Max TempDB data space used since last restart]
FROM sys.dm_resource_governor_workload_groups
      INNER JOIN sys.resource_governor_workload_groups
         ON dm_resource_governor_workload_groups.[name] =
                                resource_governor_workload_groups.[name]

To summarize:

Resource Governor was enabled, a resource pool was created, and max TempDB limit was set for the default workload group. When an operation was run that exceeded this limit, it failed as expected.

Using a Custom Workload Group

Let’s try creating a user defined workload group now. This will be under resource pool RGDemo_TempDB that we created earlier.
```
CREATE WORKLOAD GROUP RGDemo_TempDB_Group
USING RGDemo_TempDB;
```
Verify the workload group created.
You will see default and RGDemo_TempDB_Group are present under this resource pool.
```
SELECT * FROM sys.resource_governor_workload_groups;
```

Set limit - I’m assigning a max value of 2 MB for RGDemo_TempDB_Group.

ALTER WORKLOAD GROUP RGDemo_TempDB_Group WITH (GROUP_MAX_TEMPDB_DATA_MB = 2);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Verify the new workload group.

SELECT
group_id, [name], group_max_tempdb_data_mb
FROM sys.resource_governor_workload_groups;

Routing:
Create a classifier function to assign sessions from a specific application, username, or hostname to a designated workload group.
In this example, the classifier function routes sessions from 'Financials' username to RGDemo_TempDB_Group workload group, which has a TempDB data space limit of 2 MB.
If a session from 'Financials' exceeds this limit, the transaction will be stopped. All other sessions will be directed to the default workload group, which has a limit of 1 MB.
```
/*

https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-classifier-function?view=sql-server-ver17

*/
USE master;
GO
 
CREATE FUNCTION dbo.RG_UserNameClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    IF (SUSER_SNAME() = 'Financials')
BEGIN
        RETURN 'RGDemo_TempDB_Group'; 
END
    RETURN 'default';
END;
```

Now that classifier function is created, assign the classifier function to RG

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.RG_UserNameClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Verify Classifier function details

/* https://www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/tempdb-resource-governor-space-controls-in-sql-server-2025/
*/

SELECT
       resource_governor_configuration.classifier_function_id,
       resource_governor_configuration.is_enabled,
       objects.[name] AS function_name,
       sql_modules.[definition] AS function_definition
FROM sys.resource_governor_configuration
      INNER JOIN sys.objects
ON resource_governor_configuration.classifier_function_id = objects.[object_id]
      INNER JOIN sys.sql_modules
ON objects.[object_id] = sql_modules.[object_id];

Test with Financials user - Log into a separate tab in SSMS as financials user and run a temp table transaction. The operation fails because it attempts to insert data into a temp table that requires more than 2 MB of TempDB space. Since the maximum limit for the RGDemo_TempDB_Group workload group is 2 MB, any session from financials user exceeding this threshold will be stopped.
```
USE StackOverflow2013;
SELECT * INTO #temptable1 FROM dbo.Users;
```
Since the session is from Financials user, it uses RGDemo_TempDB_Group, not the default group.
To summarize:
A user-defined workload group was created, and a TempDB space limit was set for it. A classifier function was used to direct sessions from the financials user to this workload group. Since the operation exceeded the defined limit, it failed as expected. Any user other than financials would be routed to the default workload group.
Multiple resource pools, workload groups and classifier functions can be created based on the needs for different applications, usernames or hostnames.
You can also setup resource pools, to be used at peak hours and off hours.

Optionally, to clean up

/* Disable resource governor to drop classifier function */
ALTER RESOURCE GOVERNOR DISABLE;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
DROP FUNCTION IF EXISTS RG_UserNameClassifier;

/* Drop workload group. No sessions can be using this workload group. */
DROP WORKLOAD GROUP RGDemo_TempDB_Group;

/* Reconfigure resource governor to get above configuration in effect but this enables resource governor. */
ALTER RESOURCE GOVERNOR RECONFIGURE;

/* Disable resource governor */
ALTER RESOURCE GOVERNOR DISABLE;