Script to shrink Tempdb data files and Virtual Logs

ReduceTempdbSizeandVFL.sql

  
/*Get spaceused*/
sp_spaceused @updateusage=true

/*Check # of Virtual Log Files - VFL*/
dbcc loginfo

/*
DBCC DROPCLEANBUFFERS
Clears the clean buffers. 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

/*ONLY IF IS NECESSARY, THIS COMMAND AFFECT PROCEDURE AND MAY NEED TO RECOMPILE AGAIN AND CAN IMPACT PERFORMANCE*/
DBCC FREEPROCCACHE


/*DBCC FREESYSTEMCACHE
This operation is similar to FREEPROCCACHE, except it affects other types of caches.*/
DBCC FREESYSTEMCACHE ('ALL');
GO

/*FINALLY*/
DBCC SHRINKFILE (N'templog', NOTRUNCATE) -- Move allocated pages from end of file to top of file
GO
DBCC SHRINKFILE (N'templog' , 0, TRUNCATEONLY) -- Drop unallocated pages from end of file
GO
DBCC SHRINKFILE (N'templog', 20480) --Return Tempdb logfile to initial size
go
DBCC SHRINKFILE(N'templog', EMPTYFILE)
GO