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 all the way to MSSQL2016 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 declare @cmd nvarchar(100) declare @size nvarchar(6) = '2048' --- new size in MB declare cur cursor for select 'DBCC SHRINKFILE ('''+name+''', '+@size+')' from sys.sysfiles order by groupid desc OPEN CUR FETCH NEXT FROM CUR INTO @cmd while @@FETCH_STATUS =0 begin exec sp_executesql @cmd FETCH NEXT FROM CUR INTO @cmd end close cur deallocate cur GO /* Why using a curson instead of simple DBCC SHRINKFILE ('tempdev', 2048) Because we might have more than one data file for the tempDB */