Temp file not shrinking

Question

I have a temp DB file that is not shrinking. Temp DB has 6 mdf files but one is large and not shrinking after multiple attempts. Any idea on how to resolve this? Thanks

Answer 1

Restarting the SQL Server service will cause the TempDB to be recreated at its original size.

However, this may not be what you really hope to do. I frequently set TempDB to be as large as it ever gets so that queries don't need to be slowed down waiting on the database to grow.

Answer 2

The counter question is: What's the problem with?

If TempDB requries spaces, the it require it.

So may after shrinking it will growth again ... because the space is required due to the workload.

Answer 3

Hello @Babawale Dawodu ,

What the error message that you got when you shrink tempdb?  

By default, the tempdb database is configured to autogrow as needed. Therefore, this database may unexpectedly grow in time to a size larger than the desired size. Larger tempdb database sizes won't adversely affect the performance of SQL Server. When SQL Server starts, tempdb is re-created by using a copy of the model database, and tempdb is reset to its last configured size. 

You can use below command to shrink tempdb. However, please note that DROPCLEANBUFFERS will remove all the procedure cache, which may slow down some systems right after as they have to rebuild all the procedure cache again. Again, shrink your TempDB ONLY if you are running out of the space or in crucial situations.

CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC SHRINKFILE (TEMPDEV, 1024)
GO

Or DBCC DROPCLEANBUFFERS: Clears out cached indexes and data pages.

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO

We can use DBCC SHRINKDATABASE or DBCC SHRINKFILE to shrink tempdb, please refer to MS document Shrink the tempdb database to get detail information.

 Best regards,

Cathy

---

If the answer is helpful,  please click "Accept Answer" and kindly upvote it.