Shrink TempDB

These operations remove all kinds of caches, which will impact server performance to some degree until they’ve been rebuilt by the SQL Server.

Don’t do this commands unless absolutely necessary.

Do not ever put these scripts in a scheduled task

 

 

/*
DESCRIPTION    Forces the shrinking of TempDB
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2005
MSSQL2008
 

Please read Warning
*/


/*
 This will flush cached indexes and data pages. You may want to run a CHECKPOINT command first, in order to flush everything to disk.
*/

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

/*
Deletes cached execution plans. This means that ad-hoc queries and stored procedures will have to recompile the next time they run. You may notice a significant performance decrease the first few times the store procedures are run unitl the execution plans are cached again
*/
DBCC FREEPROCCACHE;
GO
DBCC FREESYSTEMCACHE ('ALL');
GO
DBCC FREESESSIONCACHE;
GO

USE [TEMPDB]
DBCC SHRINKFILE (N'tempdev', 2048);   --- New file size in MB
GO

 


Leave a Reply

Your email address will not be published. Required fields are marked *