Claim space for TempDB on Sql Server

TempDB can be shrunk, but the safest and most effective approach is to control its configured size and growth, and shrink during periods of low activity.

Key points about TempDB behavior

• TempDB is recreated on every SQL Server restart, using the last configured file sizes.
• A large TempDB does not by itself hurt performance.
• Shrink operations can cause blocking or deadlocks if TempDB is busy.

Recommended approaches

1. Prefer waiting for a restart when possible

• When SQL Server restarts, TempDB is recreated at its last configured size (set by ALTER DATABASE / DBCC SHRINKFILE / DBCC SHRINKDATABASE).
• If a restart is acceptable, first configure the desired sizes, then restart.

2. Use ALTER DATABASE (requires restart) This gives full control over the default TempDB files (tempdev, templog):

-- Run after starting SQL Server in minimal configuration mode as per docs
ALTER DATABASE tempdb MODIFY FILE
 (NAME = 'tempdev', SIZE = <target_size_in_MB>);

ALTER DATABASE tempdb MODIFY FILE
 (NAME = 'templog', SIZE = <target_size_in_MB>);

Notes:

• This method requires a restart of SQL Server.
• It only applies directly to the default logical files tempdev and templog.
• Additional TempDB files are recreated empty at startup and can then be removed with ALTER DATABASE ... REMOVE FILE if needed.

3. Use DBCC SHRINKFILE (no restart, preferred over SHRINKDATABASE) Use this to shrink individual TempDB files while SQL Server is running:

USE tempdb;
GO

-- Shrink primary data file
DBCC SHRINKFILE (tempdev, <target_size_in_MB>);
GO

-- Shrink log file
DBCC SHRINKFILE (templog, <target_size_in_MB>);
GO

Guidelines:

• Ensure the used space in each file is less than or equal to the target size, otherwise shrink will not reach that size.
• Run during low TempDB activity to reduce blocking/deadlocks.
• DBCC SHRINKFILE can reduce a file below its original size, but not below the size of the model database.

4. Use DBCC SHRINKDATABASE (no restart, database-level) If shrinking the whole TempDB database is desired:

1. Check current usage:

USE tempdb;
EXEC sp_spaceused @updateusage = TRUE;

2. Calculate target_percent based on desired free space, then run:

DBCC SHRINKDATABASE (tempdb, <target_percent>);

Limitations:

• Cannot shrink data/log files below the size specified at creation or the last explicitly set size (ALTER DATABASE ... MODIFY FILE).
• Target size depends on current used space; control is less precise than DBCC SHRINKFILE.

How to minimize impact on database service

• Perform shrink operations during maintenance windows or low workload periods.
• Stop or minimize TempDB-heavy workloads (large temp tables, heavy sorts/hashes) while shrinking.
• If strict availability is required, prefer DBCC SHRINKFILE over ALTER DATABASE (which needs restart).
• Consider whether shrinking is necessary at all; often it is better to:
  • Preallocate TempDB to a size that supports normal workload.
  • Set reasonable autogrowth (for example, fixed MB increments) to handle spikes.

Ongoing best practices

• Preallocate TempDB to a size large enough for typical workload to avoid frequent autogrowth.
• Keep TempDB files balanced in size and growth settings.
• Monitor TempDB usage regularly and adjust file sizes proactively rather than reacting with frequent shrinks.

References:

• Shrink the tempdb database
• tempdb database
• Tempdb space resource governance
• Temp Table and Temp DB Space issues - Microsoft Q&A
• SQL 2019 AlwaysOn instance - TempDB files out of balance - Microsoft Q&A