Shrink TempDB Without SQL Server Restart

Question:

How to Shrink TempDB Without SQL Server Restart?

Answer: 

Some times we are not able to shrink the tempdb files following simple shrink commands. And DBA directly suggesting for SQL services restart. Which shows lack of experience.

We can run below script and shrink the tempdb files whithout taking sql services restart.

Warning: 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 stuff unless absolutely necessary.

[Script] 

Use [Tempdb] CHECKPOINT;

GO

DBCC DROPCLEANBUFFERS;

GO 

DBCC FREEPROCCACHE;

GO

DBCC FREESYSTEMCACHE ('ALL');

GO 

DBCC FREESESSIONCACHE;

GO

DBCC SHRINKFILE (TEMPDEV, 256);   --- file size in MB

GO

DBCC SHRINKFILE (TEMP1, 256); 

*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.

*DBCC FREEPROCCACHE

Clears the procedure cache, which may free up some space in tempdb, although at the expense of your cached execution plans, which will need to be rebuilt the next time. This means that ad-hoc queries and stored procedures will have to recompile the next time you run them. Although this happens automatically, you may notice a significant performance decrease the first few times you run your procedures.

*DBCC FREESYSTEMCACHE

This operation is similar to FREEPROCCACHE, except it affects other types of caches.

*DBCC FREESESSIONCACHE

Flushes the distributed query connection cache. This has to do with distributed queries (queries between servers), but I’m really not sure how much space they actually take up in tempdb.

* DBCC SHRINKFILE

DBCC SHRINKFILE is the same tool used to shrink any database file, in tempdb or other databases. This is the step that actually frees the unallocated space from the database file.

Warning: Make sure you don’t have any open transactions when running DBCC SHRINKFILE. Open transactions may cause the blocking and DBCC operation will fail , and possibly corrupt your tempdb !