Script to shrink tempdb

Script to shrink tempdb

In general shrinking a database is easy. This operation is done when an administrator wants to make database files smaller. It can be even done with SQL Server Management Studio. It becomes a little more complex when the database is tempdb. It is a system database that stores different types of temporary objects. As a consequence, it cannot be easily shrinked to reduce occupied disk space. This post contains a script that allows for that.

USE [TEMPDB];
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
DBCC FREESYSTEMCACHE ('ALL');
GO
DBCC FREESESSIONCACHE;
GO
DBCC SHRINKFILE (TEMPDEV, 1024);
GO

It shrinks tempdb data file to 1024 MB.