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'); GOYes, 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
Nick.McDermaid 6 years ago
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