TempDB Sized Differently

What this check looks for

The distinct sizes of tempdb’s data files, from tempdb.sys.database_files where type = 0. If there is more than one distinct size, the check fires. Log files are not included, because tempdb has one log file and it is not part of this.

Why it matters

SQL Server fills files in a filegroup proportionally to their free space, and that behaviour turns unequal files into one busy file.

The reason to have several tempdb data files is allocation contention. Every file has allocation bitmap pages, the GAM, SGAM and PFS pages, and every allocation has to latch them. With one file, a busy instance queues on those latches, which appears as PAGELATCH_UP and PAGELATCH_EX waits on pages 1:1, 1:2 and 1:3. Adding files multiplies the number of bitmap pages, which spreads the contention.

Proportional fill undoes this when the files are unequal. SQL Server sends allocations preferentially to the file with the most free space, so a file that is twice the size of its neighbours receives roughly twice the work. In the worst case one file takes nearly all the allocations, every allocation latches the same bitmap pages, and you have the contention of a single file while paying for several.

Files usually become unequal by accident. Somebody creates four files of the same size, autogrowth is set per file, one file happens to fill first and grows, and from then on proportional fill favours it, so it fills first again and grows again. The imbalance is self-reinforcing, which is why it is worth correcting rather than waiting for it to even out.

How to confirm it yourself

SELECT [name]                                                  AS [logical_name],
       CAST([size] * 8.0 / 1024 AS DECIMAL(12,1))              AS [size_mb],
       CAST(FILEPROPERTY([name], 'SpaceUsed') * 8.0 / 1024 AS DECIMAL(12,1)) AS [used_mb],
       CASE WHEN [is_percent_growth] = 1
            THEN CAST([growth] AS VARCHAR(10)) + ' %'
            ELSE CAST([growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [growth],
       [physical_name]
  FROM tempdb.sys.database_files WITH (NOLOCK)
 WHERE [type] = 0
 ORDER BY [size] DESC;

Whether the contention this is meant to prevent is actually happening:

SELECT [wait_type], [waiting_tasks_count],
       [wait_time_ms] / 1000 AS [wait_time_seconds]
  FROM sys.dm_os_wait_stats WITH (NOLOCK)
 WHERE [wait_type] LIKE 'PAGELATCH%'
 ORDER BY [wait_time_ms] DESC;

How to fix it

Make every tempdb data file the same size and give them all the same growth setting.

The clean way is to size them all up to match the largest, which needs no restart and loses nothing:

USE [master];
GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev',  SIZE = 8GB, FILEGROWTH = 512MB );
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev2', SIZE = 8GB, FILEGROWTH = 512MB );
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev3', SIZE = 8GB, FILEGROWTH = 512MB );
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev4', SIZE = 8GB, FILEGROWTH = 512MB );

Growing files is online. Shrinking them to match the smallest is the other direction and is not reliable while tempdb is in use, so if you want them smaller, set the sizes you want and restart, since tempdb is recreated from these settings at every startup.

Then stop it happening again:

• Give every file an identical fixed growth increment. Different growth settings reintroduce the imbalance.
• Size tempdb for what it actually needs, so autogrowth is rare. A tempdb that never grows never becomes unequal.
• On SQL Server 2016 and later this is handled for you. All files in tempdb grow together by default, which is what trace flag 1117 used to do and is now the built in behaviour for tempdb. On SQL Server 2014 and earlier, enable trace flags 1117 and 1118 as startup parameters.

How many files? The usual guidance is one file per logical processor up to eight, then add four at a time only if contention persists. More files than that rarely helps and makes every file smaller.

How long it takes

About four hours, mostly because getting the sizes right usually means understanding what tempdb is being used for first, and because a restart may be needed to shrink.

Related reports

Related checks

Frequently asked questions

Does one file being slightly larger matter? A few megabytes, no. The check reports any difference because a difference is always the start of one, and the fix is the same either way.

We are on SQL Server 2019. Is this handled automatically? Autogrowth of all tempdb files together is automatic from 2016 onwards, which stops them diverging. It does not equalize files that are already unequal, which is what this finding is about.

How many tempdb files should we have? One per logical processor up to eight. Beyond eight, add more only if PAGELATCH waits on tempdb are still significant.

Can I shrink one file to match the others? DBCC SHRINKFILE on a tempdb file in use is unreliable and can fail or produce odd results. Set the sizes you want and restart, which recreates tempdb from the settings.