Change the Database Recovery Model to "Simple" under option and Shrink the "temp" data file once more.
Once done, return the Recovery Model option to "Full".
Temp db files showing different size
![](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(99.2, 45%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
SVA
121
Reputation points
Hi
TempDB file size showing different in disk and database.
In disk tempdev showing the size as 31.2 GB but in tempdb database it is showing 16384.00 MB.
file name is tempdb.mdf
Tried to shrink but not shrinking.
SQL Server | Other
{count} votes
-
PandaPan-MSFT 1,931 Reputation points2022-09-12T02:55:16.453+00:00 Hi @SVA ,
Have you solved your problem already? If so pls mark the answer and if you still have some poblems, pls feel free to let me know.
Sign in to comment
4 answers
Sort by: Most helpful
-
Noel Kipyegon 1 Reputation point2022-09-05T12:17:29.007+00:00 -
Bjoern Peters 8,921 Reputation points2022-09-05T15:02:21.413+00:00 And how should he handle this problem if tempdb is already in SIMPLE mode?
-
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator2022-09-05T20:53:41.627+00:00 If? Tempdb is in simple recovery, period.
As for the original questoin, I wonder if the poster may be looking at two different tempdbs on different instances.
Sign in to comment -
-
Olaf Helper 47,586 Reputation points2022-09-05T14:01:41.487+00:00 but in tempdb database it is showing 16384.00 MB.
Where is that value shown?
-
Bjoern Peters 8,921 Reputation points
2022-09-05T15:04:27.967+00:00 Hi @SVA ,
as @Olaf Helper stated :
It would be nice to see some screenshot of your SSMS and your Filesystem to better understand what you see in which location.
-
PandaPan-MSFT 1,931 Reputation points
2022-09-06T01:47:46.367+00:00 Hi @SVA ,
Actually, If you restart the SQL Server, tempdb will rebuild automatically. If you can't restart the SQL Server for some reasons. You may try the following order,which i'm not sure if you had tried already...
USE [tempdb]
GODBCC FREESYSTEMCACHE ('ALL')
GO
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"
-
PandaPan-MSFT 1,931 Reputation points
2022-09-06T02:12:20.09+00:00 Hello, @SVA , you may use the following order to see if you have shrinked the size of tempdb to the minimal.
use tempdb
dbcc shrinkfile (tempdev)
dbcc shrinkfile (templog)
-
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator
2022-09-06T20:48:20.237+00:00 Why do you think flushing all caches in SQL Server would help to reduce the size on tempd on disk???
Furthermore, since yuo flush lal caches, a lot of data will have to be read from disk, lots of queries be compiled etc. So, this would certainly cause a dip in performance.
Please don't post bad advice! In this case, the poster have given too little information for us to be able to answer, so just await more information.
Sign in to comment -