Shrink Tempdb in production

DBA are always been asked a general question - 'How can you handle a tempdb in production when it is growing fast' In many times the questioner actually don't know the answer. This is because there is no such straight forward answer for this question. However, this doesn't mean that we can't do anything about it.

 Step 1 - Add another files and point it the the drive where there are more space

USE [master] GO 
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempDev2', FILENAME = N'D:\tempdb\templog2.ndf' , SIZE = 3072KB , FILEGROWTH = 10%) 
GO 

 Step 2 - Stop auto growth

USE [master] GO ALTER DATABASE [tempdb] 
MODIFY FILE ( NAME = N'tempdev', FILEGROWTH = 0) 
GO 

Step 3 (optional) Find the actual space

USE tempdb; GO EXEC sp_spaceused @updateusage = N'TRUE'; 
GO 
DBCC OPENTRAN 

Step 4  Let's shrink the file
USE [tempdb] 
GO 
DBCC SHRINKFILE (tempdev, 1024) 
GO

** Do NOT forget to start the auto growth back again on the stopped one.

In non-production environment you can do this 

 -- DBCCDROPCLEANBUFFERS
/*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 

--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 FREEPROCCACHE
GO 

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

--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 FREESESSIONCACHE; 
GO 

-- Let's shrink
/*Make sure you don’t have any open transactions when running DBCC SHRINKFILE. Open transactions may cause the DBCC operation to fail, and possibly corrupt your tempdb */

DBCC SHRINKFILE (TEMPDEV, 2080); 

--- New file size in MB
--- Don't use less than 1024 MB