Quick Scan Report – TempDB Showing Growth

What this check looks for

TempDB data files whose current size is larger than the size they were created at, meaning they have autogrown since the instance started. The message names the file.

Why it matters

TempDB is recreated from its configured size every time SQL Server starts. If it has grown since then, the configured size is wrong, and it will be wrong again after every restart.

That produces a repeating cost rather than a one-off one:

• After every restart, tempdb starts too small and has to grow its way back to the size the workload actually needs.
• Every growth event happens while work is running, by definition. TempDB is only busy when queries are sorting, spilling, using temp tables or holding version store rows, so the growth always lands in the middle of real work.
• During a growth, allocations against that file pause. On a busy instance this shows up as a stall that is difficult to attribute, because the query that triggers the growth is not necessarily the one that suffers.
• Instant file initialization does not help the log file. Data file growth is instant if the service account holds Perform volume maintenance tasks; log growth is always zero-filled and always takes time proportional to the size.
• Performance is worse for the first days after a restart, which is the pattern the check description names: a system that feels slow for a week after a reboot and then settles, as tempdb reaches its working size.

And it degrades the multi-file arrangement. If several tempdb data files grow independently, they end up different sizes, and proportional fill then sends most allocations to the largest one. The whole point of multiple files is to spread allocation across several sets of bitmap pages, and unequal files undo it. With percentage growth this is guaranteed, because each file grows by a percentage of its own differing size.

What makes tempdb grow is worth knowing before sizing it, because sometimes the right fix is upstream:

• Sort and hash spills, from plans with bad estimates. A query expecting 100 rows and getting 10 million spills its sort to tempdb.
• Temp tables and table variables, particularly in loops.
• The version store, if read committed snapshot isolation or snapshot isolation is on, or if a long running transaction holds versions.
• Online index rebuilds with SORT_IN_TEMPDB.
• Triggers and MARS, which use the version store regardless of isolation level.

How to confirm it yourself

Configured size against current size, which is the finding directly:

SELECT mf.[name]                                                   AS [logical_name],
       CAST(mf.[size]  * 8.0 / 1024 AS DECIMAL(12,1))              AS [configured_mb],
       CAST(df.[size]  * 8.0 / 1024 AS DECIMAL(12,1))              AS [current_mb],
       CAST((df.[size] - mf.[size]) * 8.0 / 1024 AS DECIMAL(12,1)) AS [grown_by_mb],
       CASE WHEN df.[is_percent_growth] = 1
            THEN CAST(df.[growth] AS VARCHAR(10)) + ' %'
            ELSE CAST(df.[growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [growth_setting],
       mf.[physical_name]
  FROM sys.master_files           AS mf WITH (NOLOCK)
 INNER JOIN tempdb.sys.database_files AS df WITH (NOLOCK) ON df.[file_id] = mf.[file_id]
 WHERE mf.[database_id] = 2
 ORDER BY mf.[type], mf.[file_id];

sys.master_files holds what tempdb is recreated at; tempdb.sys.database_files holds what it has become. The difference is the amount you should add to the configured size.

How much is actually in use, and what is using it:

SELECT SUM(user_object_reserved_page_count)     * 8 / 1024 AS [user_objects_mb],
       SUM(internal_object_reserved_page_count) * 8 / 1024 AS [internal_objects_mb],
       SUM(version_store_reserved_page_count)   * 8 / 1024 AS [version_store_mb],
       SUM(unallocated_extent_page_count)       * 8 / 1024 AS [free_mb]
  FROM tempdb.sys.dm_db_file_space_usage;

That breakdown points at the cause. Large internal objects mean spills; large user objects mean temp tables; a large version store means snapshot isolation or a long transaction.

Who is consuming it right now:

SELECT TOP (20)
       t.[session_id],
       s.[login_name],
       s.[host_name],
       s.[program_name],
       (t.[user_objects_alloc_page_count] - t.[user_objects_dealloc_page_count]) * 8 / 1024
                                                       AS [user_objects_mb],
       (t.[internal_objects_alloc_page_count] - t.[internal_objects_dealloc_page_count]) * 8 / 1024
                                                       AS [internal_objects_mb],
       qt.
  FROM tempdb.sys.dm_db_session_space_usage AS t
 INNER JOIN sys.dm_exec_sessions AS s WITH (NOLOCK) ON s.[session_id] = t.[session_id]
  LEFT JOIN sys.dm_exec_requests AS r WITH (NOLOCK) ON r.[session_id] = t.[session_id]
 OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
 WHERE t.[session_id] > 50
 ORDER BY [internal_objects_mb] DESC;

How long the instance has been up, which tells you whether the current size is representative:

SELECT [sqlserver_start_time],
       DATEDIFF(HOUR, [sqlserver_start_time], GETDATE()) AS [hours_up]
  FROM sys.dm_os_sys_info WITH (NOLOCK);

An instance up for two hours has not seen its month end job yet. Size from a period that includes your heaviest work, not from a quiet Tuesday morning.

How to fix it

Set the configured size to what tempdb actually grows to, in equal files, with a fixed growth increment. The size change takes full effect at the next restart.

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);

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

Growing the files is online and immediate. Reducing the configured size takes effect at the next restart, since tempdb is rebuilt then.

How to choose the size: take the total tempdb has grown to under real load, add headroom for the heaviest thing it does (usually month end or the index maintenance window), and divide by the number of data files. Sizing it so it never autogrows is the target, because an autogrow on tempdb is always badly timed.

All data files the same size, with the same growth increment. This is not optional if you have several; unequal files defeat the reason for having them.

Use megabytes, not a percentage. Percentage growth guarantees the files diverge and produces increasingly large growth events as they get bigger.

Give the whole drive to tempdb if you can. Tempdb has no reason to leave free space on its volume, since nothing else should be there. Sizing the files to fill the drive means it never grows and never competes.

Check instant file initialization is on, which makes data file growth nearly free when it does happen:

SELECT [servicename], [service_account], [instant_file_initialization_enabled]
  FROM sys.dm_server_services;

If it is disabled, granting Perform volume maintenance tasks to the service account and restarting turns a multi-second data file growth into a near instant one. It does not help the log file, which is always zero-filled.

Then look at what is driving the usage, because sizing around a problem is only half a fix. Large internal object usage means query plans are spilling, and fixing the estimate that causes the spill reduces the tempdb requirement rather than accommodating it. The tempdb consumers and high usage reports are where that starts.

How long it takes

About two hours, most of it establishing the right size from real workload. The ALTER is immediate for growth; a reduction applies at the next restart.

Related reports

Related checks

Frequently asked questions

Does it matter if tempdb grows once and then settles? It matters after every restart, because tempdb is recreated at the configured size each time. The growth is not a one-off, it repeats on every startup.

How big should tempdb be? Large enough that it never autogrows under your heaviest workload. Measure what it reaches over a period that includes month end, and configure that, divided evenly across the data files.

Can I shrink it back down? Set the configured sizes you want and restart. Shrinking a tempdb file in use is unreliable, and a restart rebuilds it from the configuration cleanly.

Instant file initialization is on, so is growth still a problem? It removes most of the cost for data files, not all of it, and it does nothing for the log file. Correct sizing is still the right answer.