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

Is it risky to use the following?

use tempdb

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

-- report the new file sizes
SELECT name, size
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
  • 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 Sep 8, 2014 at 11:40
  • Yes, I know. But when I have to, cause it's to late to be proactive :) Is this the best solution?
    – Mcol
    Sep 8, 2014 at 11:42
  • Sorry I can't help you there. Sep 8, 2014 at 11:48

2 Answers 2


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?
    – Mcol
    Sep 8, 2014 at 13:56
  • 1
    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.
    – dattatraynale
    Sep 9, 2014 at 8:55

Is a problem, maybe you can use the Whoisactive procedure that captures the data referring to the use of tempdb and identifies the processes that are causing this high consumption. Cheers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.