MSSQL Shrink tempdb

11/04/2016

Hier ein kleines Snippet, um nachträglich eine zu groß gewordene tempdb vom MSSQL Server wieder in den Griff zu bekommen.

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

Quelle: http://dba.stackexchange.com/questions/78383/best-practice-to-shrink-tempdb-in-a-production-environment