30

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
Mcol
  • 449
  • 1
  • 4
  • 3

1 Answers1

15

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? –  Sep 08 '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. –  Sep 09 '14 at 08:55