Best practice to shrink Tempdb in a production environment

  • What is best practice to use when shrinking Temporary db in SQL Server 2008?

    Is it risky to use the following?

    use tempdb
    GO
    
    DBCC FREEPROCCACHE -- clean cache
    DBCC DROPCLEANBUFFERS -- clean buffers
    DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
    DBCC FREESESSIONCACHE -- clean session cache
    DBCC SHRINKDATABASE(tempdb, 10); -- shrink tempdb
    dbcc shrinkfile ('tempdev') -- shrink db file
    dbcc shrinkfile ('templog') -- shrink log file
    GO
    
    -- report the new file sizes
    SELECT name, size
    FROM sys.master_files
    WHERE database_id = DB_ID(N'tempdb');
    GO
    

    Best practice is to work out what is making it grow, and address that. If you shrink it it just has to grow again and that takes time and IO

    Yes, I know. But when I have to, cause it's to late to be proactive :) Is this the best solution?

    Sorry I can't help you there.

  • It is a best practice to proactively monitor the normal usage of Tempdb and set the size accordingly. If this is one off case where the Tempdb has grown to such a size and its a PROD env, I would restart SQL Server Services during weekly maintenance. There after Tempdb would go back to its configured size.

    Shrinking the file is fine as long as Tempdb is not being used, else existing transactions may be impacted from performance point of view due to blockings and deadlocks.

    Cleaning procedure cache, buffer caches etc will have negative impact on the database performance itself until those are not re-created. I would not do this on PROD.

    Hope that helps!

    Thanks for godd input. Is it enough to check with sp_who for processes in tempdb?

    I dont think that is a reliable way to find out if temp db is being used. I think that would just show up only if someone is creating temp table directly in SSMS directly. But if the same is being done as a result of a query operation due to memory spill etc then it would not show up in sp_who2. That question would infact be a separate thread. Please create that as it is a separate discussion. If the previous answer helped you please mark that as answer. That will help others with similar situation.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM