Sometimes we try to Shrink TempDB but it don’t happen then We can Shrink TempDB Database without restart the SQL Server through below Steps.
- Check the Size of TempDB
sp_helpdb
- You may want to run a CHECKPOINT command first, in order to flush everything to disk
CHECKPOINT; GO
- Clears the clean buffers. This will flush cached indexes and data pages.
DBCC DROPCLEANBUFFERS GO
- Clears the procedure cache, which may free up some space in tempdb
DBCC FREEPROCCACHE; GO
- Clears the procedure cache
DBCC FREESYSTEMCACHE ('ALL');
GO
- Flushes the distributed query connection cache.
DBCC FREESESSIONCACHE; GO
Warning:- You have to Make sure that don’t have any open transactions when running DBCC SHRINKFILE .Open transactions may cause the DBCC operation to fail, and possibly corrupt your tempdb.
use tempdb go DBCC SHRINKFILE (TEMPDEV, 10); --file size in MB GO
- Again check the size
sp_helpdb --Check The new Size