SQL Server: shrink tempdb after clearing the caches
Clear caches with DBCC, then shrink tempdb files with DBCC SHRINKFILE.
When the tempdb log file has grown and you need to reclaim disk space, you can clear the caches and then shrink the file with DBCC SHRINKFILE.
USE [tempdb]
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
--DBCC SHRINKFILE ('tempdev', 1024)
DBCC SHRINKFILE ('templog', 1024)
GO
How it works
| Command | Effect |
|---|---|
DBCC FREEPROCCACHE |
Clears the execution plan cache |
DBCC DROPCLEANBUFFERS |
Clears clean pages from the buffer pool |
DBCC FREESYSTEMCACHE ('ALL') |
Clears all system caches |
DBCC FREESESSIONCACHE |
Clears the distributed query session cache |
DBCC SHRINKFILE ('templog', 1024) |
Shrinks the logical file templog to 1024 MB |
The tempdev line (data file) is commented out; uncomment it to shrink the data file too.
Notes
- Clearing the caches temporarily hurts performance (plan recompilation, page re-reads): avoid it under load on a production server.
- Shrink only releases unused space; with an open transaction or active tempdb usage it may fail or free nothing.
- Check the logical file names with
SELECT name FROM tempdb.sys.database_files.