How To Shrink The TEMPDB Database in SQL SERVER ?

Occasionally, Every DBA will bump across this issue, Tempdb database is getting full, Tempdb Drive has exceeded the disk threshold and need to take action to reduce the file size.

DBAs having little experience would think of shrinking the database using the GUI, Right Click on the Database > Tasks > Shrink > Database / Files and then shrink the database accordingly.

DBAs with some experience would think of running the command DBCC Shrinkfile ('Filename', target_in_MB)

However, with both the Solutions / Steps,  files / database doesn't shrink  and you can see that the file size is not having any impact.

So, here  is a couple of solutions you can try to shrink the Tempdb database and reduce the size on the drive accordingly.

1.  Try running the following command :-

USE TEMPDB
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ('ALL')
GO

and then run the command.

DBCC SHRINKFILE ('FILENAME', TARGET_SIZE_IN_MB)
GO

If there are more than one Tempdb data ( Tempdev )  file , you can use those files seperately to run the DBCC Shrinkfile command to shrink the file size after clearing the DBCC System , Session and Proc Caches

These are the DBCC commands to free up some of the SQL SERVER Memory Caches.

DBCC FREESYSTEMCACHE ('ALL')
Clears all unused cache entries from all the caches.

DBCC FREESESSIONCACHE
Flushes all the distributed query connections' cache that are used against the instance of Microsoft SQL Server.

DBCC FREEPROCCACHE
Clears all elements from the procedure cache.
Freeing the procedure cache would cause, an ad-hoc SQL statement to be recompiled rather than reused from the cache.


2.  Here's another solution which can help to shrink the tempdb database.

Add a few MB to the data file and then try shrinking the file., it will release some space to the OS.

For Example:- when you find the tempdb is growing and has a couple of hundreds of MBs to reach the threshold. You can try shrinking the database normally. If there's any bulk transaction running against the instance or If there's a replication set up on the database.
Then, you can increase the tempdb data file to a few MBs and then try shrinking the file using the DBCC command to claim more space.

-Cheers