LLI Wiki — My Digital Knowledge Base

SQL Server: shrink tempdb without restarting the instance

Shrink tempdb files with DBCC SHRINKFILE without a restart, with a link to the reference article.

When tempdb has grown exceptionally, you can try to shrink its files without restarting SQL Server, using DBCC SHRINKFILE. Reference: SQL Sunday, Shrinking tempdb without restarting SQL Server.

Procedure

List the tempdb files and their size:

USE tempdb;
SELECT name, type_desc, size * 8 / 1024 AS size_mb
FROM sys.database_files;

Shrink a file to a target size (in MB):

USE tempdb;
DBCC SHRINKFILE (tempdev, 1024);  -- data file, target 1024 MB
DBCC SHRINKFILE (templog, 512);   -- log file, target 512 MB

Notes

Mastodon LLIMastodon SocialFosstodon