Post reply

Dont want to restart Tempdb not able to shrink datafiles in ag primary

  • July 24, 2024 at 12:43 pm

    #4439548

    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

    #4439658

    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

    #4442648

    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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply