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
- The logical name (
tempdev,templog, or the name of any additional file) is the one in thenamecolumn. - The shrink fails or stays partial while temporary objects, row versions or sessions use pages at the end of the file; the cited article describes how to deal with that.
- Shrinking tempdb is an exceptional operation: if it grows back, find the cause (query, sort, version store) instead of shrinking repeatedly. Tempdb is recreated at its configured size on restart anyway.