Why TempDB Is Not Shrinking Even The Available Free Space Is 99%

Shrinking databases is not a regular task that we perform as DBAs but once in awhile we need to shrink database files reclaim space and release to OS. It will just work for user databases but for tempdb there is a strange behaviour.

I use the below T-SQL command to shrink a data/log file.

dbcc shrinkfile (N'tempdev_4' , 7200)

Note: The target value should be in MB.

No matter how many times you shrink, the file size will not change. If you carefully observed, you could see the file has more free space availbe.

The trick with the tempdb is, you might need to clean the cache first and then try shrinking it again:

dbcc freeproccache

disclaimer Be aware running this command in prod, it will have a negative impact to performance specially for busy OLTP system because it clears all the plan cache.

However, sometime even freeproccache won’t work. In that case, you may need to try the command below:

dbcc freesystemcache ('all')

Note: Same disclaimer is applied here as well.

It is the same behaviour even in SQL Server 2019, the latest RTM at the time of this writing.

About the post header picture: It was taken during the Fall Color Trip 2020 at Independence Pass, Colorado on Sep 29th, another angle at Independance Pass.