Skip to content

Instantly share code, notes, and snippets.

@DataSlugger
Created May 16, 2020 23:40
Show Gist options
  • Save DataSlugger/11ccf089c6ee2efcfda4a7dbe19f6fde to your computer and use it in GitHub Desktop.
Save DataSlugger/11ccf089c6ee2efcfda4a7dbe19f6fde to your computer and use it in GitHub Desktop.
Script to shrink Tempdb data files and Virtual Logs
/*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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment