LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon