How to Shrink TempDB Database Without Restart SQL Server Services?


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

 


 

Leave a Reply

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