25

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 – Nick.McDermaid Sep 8 '14 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 '14 at 11:42
  • Sorry I can't help you there. – Nick.McDermaid Sep 8 '14 at 11:48
11

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 '14 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 '14 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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