TempDB files too small.

What this check looks for

Data files in tempdb smaller than 1 MB, from sys.master_files where database_id = 2. The message names the file and its size in kilobytes.

Why it matters

A file this small fails at both jobs a tempdb data file has.

It grows constantly. Tempdb is the busiest allocator on the instance, and a 1 MB file fills almost immediately. Every growth event pauses allocations against that file, and on a busy system this repeats continuously. Growth events on tempdb happen under load by definition, because tempdb is only busy when something is running.

And it is not sharing the work. SQL Server fills files proportionally to their free space, so a file with a megabyte of space receives a proportionally tiny share of allocations. The whole reason to have several tempdb data files is to spread allocation across several sets of bitmap pages, and a file that receives almost no allocations is not spreading anything. You have the contention profile of however many files are actually sized properly.

So the finding is really two problems at once: a file that thrashes, and a file that is not contributing.

How it happens:

• A file added and never sized. Somebody read the advice about multiple tempdb files, added three more, and left them at the default 1 MB with 10 percent growth. Six months later the original file is 20 GB and the others are a few hundred megabytes, which is the unequal file problem as well.
• Tempdb recreated at a restart from a configuration that was never corrected.
• A file added during an incident to relieve a full tempdb, at whatever size was quick.

The related finding is nearly always present too. A file at 1 MB alongside files at several gigabytes is unequal by definition, and the unequal file check covers why that undoes the benefit of having several.

How to confirm it yourself

SELECT [file_id],
       [name]                                                    AS [logical_name],
       [size] * 8                                                AS [size_kb],
       CAST([size] * 8.0 / 1024 AS DECIMAL(12,1))                AS [size_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 sys.master_files WITH (NOLOCK)
 WHERE [database_id] = 2 AND [type] = 0
 ORDER BY [size];

Read the whole list, not just the small ones. If the sizes vary at all, the unequal file problem applies and it matters more than the smallest file’s size.

The live sizes, which differ from sys.master_files once tempdb has grown since startup:

SELECT [name],
       CAST([size] * 8.0 / 1024 AS DECIMAL(12,1))                             AS [current_mb],
       CAST(FILEPROPERTY([name], 'SpaceUsed') * 8.0 / 1024 AS DECIMAL(12,1))  AS [used_mb]
  FROM tempdb.sys.database_files WITH (NOLOCK)
 WHERE [type] = 0
 ORDER BY [size];

sys.master_files shows the configured size tempdb is recreated at; tempdb.sys.database_files shows what it has grown to since the last restart. Both matter: the first is what you are fixing, the second tells you what size to fix it to.

Whether growth is actually happening:

SELECT [DatabaseName], [FileName], [StartTime], [Duration] / 1000 AS [duration_ms]
  FROM sys.fn_trace_gettable(
         CONVERT(NVARCHAR(500),
           (SELECT [value] FROM sys.fn_trace_getinfo(1) WHERE [property] = 2)), DEFAULT)
 WHERE [EventClass] = 92 AND [DatabaseName] = 'tempdb'
 ORDER BY [StartTime] DESC;

How to fix it

Size every tempdb data file the same, with the same growth increment. This is online and takes effect immediately for growth; the configured size applies fully at the next restart.

USE [master];
GO

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

Growing files is online, so bringing the small ones up to match happens immediately. Shrinking one to match the others is unreliable on a tempdb in use, so if you need them smaller, set the sizes you want and restart, since tempdb is rebuilt from the configuration.

What size? Look at what tempdb actually grows to under real load, from the second query above, and divide by the number of files. Sizing it so it never needs to autogrow is the goal, because an autogrow on tempdb always happens at a busy moment.

Use a fixed growth increment in megabytes, not a percentage. Percentage growth on tempdb guarantees the files diverge, since each grows by a percentage of its own differing size.

Check the drive has room for the total before setting it, and that tempdb is not on the C: drive, both of which have their own checks.

On SQL Server 2014 and earlier, enable trace flag 1117 so all files in the filegroup grow together, which keeps them equal. From 2016 that is the default behaviour for tempdb.

How long it takes

About an hour, most of it working out the right size. Growing the files is immediate.

Related reports

Related checks

Frequently asked questions

Does file size matter if it can grow? Yes. Growth on tempdb always happens under load, and a file that starts at 1 MB spends its life growing rather than working.

Why does a small file not help with contention? Proportional fill sends allocations to the file with the most free space. A tiny file receives almost none, so its bitmap pages are barely used and it is not spreading anything.

Can I shrink the big file instead of growing the small ones? Shrinking a tempdb file in use is unreliable. Set the configured sizes you want and restart, and tempdb is rebuilt to them.

Do I need a restart? Not to grow files, which is online. To reduce a file, yes, in practice.