How to force a shrink of the MSSQL TempDB

In a bind for disk space on a MSSQL server that you cannot restart? Here’s a way to force a shrink of the TempDB if that’s your issue. Be aware this can negatively impact performance since you will be clearing the execution cache for the server. The cache that SQL uses to store execution plans after they are compiled. Meaning they have to be recompiled. But working for a small host with shared database servers that CANNOT go down during the day I’ve been in this bind. This is the script I found to fix the issue.

DBCC FREEPROCCACHE
GO
use tempdb
GO
— Shrink tempDB data file
DBCC SHRINKFILE (‘tempdev’ , 1 )
go
— Shrink tempdb log file
dbcc shrinkfile (‘templog’ ,1 )

This won’t always work the first time, so just keep executing it until the TempDB goes down. I usually have to run this up to 10 times before the TempDB gets down to less than a 100MB. By default the TempDB should be around 7MB when it starts. Also. restarting the SQL server should empty the database and re-intialize it so to speak. Each time you start SQL server the TempDB should be about 7MB. Of course you should probably figure out what is filling your TempDB, but when you have 700 databases on one MSSQL server and you don’t control any of them or know the developers, that’s easier said than done.

Leave a Comment

Your email address will not be published.