July 24, 2024 at 12:43 pm
HI All,
Dont want to restart Tempdb not able to shrink datafiles in always on server primary
is there any alternative way we can reduce the tempdb files and shrink the space from them .
can we run this below command to clear the space from tempdb data files without restart and effect always on primary server
DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
Thanks
Naga.Rohitkumar
July 24, 2024 at 8:22 pm
Just my 2 cents - shrinking any database is not a good idea. The DB grew to that size for a reason, and tempdb grew likely due to queries being run that needed the temporary storage. Shrinking tempdb will fix the issue (reclaim disk space) but not the problem (queries using a lot of tempdb space) and tempdb will just re-grow to the same size.
I also wouldn't recommend clearing caches and buffers as, if I am not mistaken, those don't reside in tempdb, they are in memory. So clearing all of your caches and buffers won't impact tempdb size.
But, assuming you cleared out the space in tempdb, you won't get the disk space back unless you shrink the DB or restart the instance. Those are the only 2 ways to get disk space back from tempdb. Running any of the commands you listed won't make tempdb any smaller.
The ONLY way to make any SQL file smaller on disk is to shrink it. If you can't shrink, the file won't get any smaller.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 6, 2024 at 2:44 am
I do a CHECKPOINT first but, yes, those are the commands I use that help free up temporary objects in TEMPDB before shrinking the database. It doesn't work every time but does usually make an appreciable difference.
September 25, 2024 at 8:08 am
DBCC SHRINKFILE ?
You want to do this on the primary. I know growing them will propagate the same to the secondary nodes, but have not done a shrink before. You may have to try multiple times to get the file in a state where it has no transactions going.
----------------------------------------------------
October 1, 2024 at 4:39 pm
So there is an alternative if tempdb has "gone wild" and this isn't ordinary growth.
The commands above will help you, as you surmise, but plans will have be recreated and the cache repopulated which will cause some initial performance slowness. What I usually do is this:
CHECKPOINT;
DBCC FREEPROCCACHE; -- clean cache
DBCC DROPCLEANBUFFERS; -- clean buffers
DBCC FREESYSTEMCACHE ('ALL'); -- clean system cache
DBCC FREESESSIONCACHE; -- clean session cache
CHECKPOINT; --Yes again, and checkpoint takes a while to run.
Then flip the servers briefly to the local secondary, which does almost the same thing as restarting the services insofaras cleaning that tempdb cache. Then you can flip back to original primary after you're sure things are going smoothly. I include this because of your use of the phrase "primary server" indicates you might be on an availability group. If you're not, then you may have no other choice than to restart the services.
And use DBCC SHRINKDATABASE(tempdb, TRUNCATEONLY), not shrink files unless you absolutely are in a crunch.
October 1, 2024 at 4:40 pm
Additional note: If TRUNCATEONLY isn't working, check min file sizes on the tempdb files. They may be larger than they are supposed to be. ALTER the files to resize and try TRUNCATEONLY again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply