This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
Hello @Babawale Dawodu,
Just friendly follow up this case, did the reply could help you? If the response helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(304, 36%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDL%3C/text%3E%3C/svg%3E)
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.
Thanks David.
Is there any other way aside from restarting SQL server? The other temp files do truncate easily except one, and I'm experiencing this on multiple servers.
and I'm experiencing this on multiple servers.
This is where I get really scared.
Listen closely now: shrinking files is a very exceptional activity. Like, there was an accident or other one-off operation that caused a file to grow. But it is not a routine action that you conduct all over the shop.
Your tempdb should be as big as the workload requires. If you shrink it, it will only grow again. And as David says, that takes resources, and only slows down queries.
Then again, you want your data files for tempdb to be the same size. But the solution to that is to manually grow the other to the size of the big one.
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.
The file is hoarding storage space. Example; File size is 30GB, and free space is 29GB. Why can't the space be freed and used elsewhere?
File size is 30GB, and free space is 29GB.
Really? We talk about 30GB, really? I have an USB stick with 512GB storage.
A 1TB SSD costs around 70 $ and you have an issue with 30 GB; really?
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.