How to shrink the tempdb database in SQL Server?

Method 1 to Shrink Tempdb

This method requires you to restart SQL Server.

Stop SQL Server. Start SQL Server by typing the following command:
sqlservr -c -f
Run Following Command

ALTER DATABASE tempdb MODIFY FILE

(NAME = 'tempdev', SIZE = target_size_in_MB) 

ALTER DATABASE tempdb MODIFY FILE

(NAME = 'templog', SIZE = target_size_in_MB)

Stop SQL Server by pressing Ctrl-C at the command prompt window, restart SQL Server as a service

 

Method 2 to Shrink Tempdb

Use the DBCC SHRINKDATABASE command to shrink the tempdb database as a whole. DBCC SHRINKDATABASE receives the parameter target_percent, which is the desired percentage of free space left in the database file after the database is shrunk. If you use DBCC SHRINKDATABASE, you may have to restart SQL Server.

dbcc shrinkdatabase (tempdb, 'target percent') 

 

There are limitations for use of the DBCC SHRINKDATABASE command on the tempdb database. The target size for data and log files cannot be smaller than the size specified when the database was created or the last size explicitly set with a file-size changing operation such as ALTER DATABASE with the MODIFY FILE option or the DBCC SHRINKFILE command. Another limitation of DBCC SHRINKDATABASE is the calculation of the target_percentage parameter and its dependency on the current space used.

 

Method 3 to Shrink Tempdb

Use the command DBCC SHRINKFILE to shrink the individual tempdb files. DBCC SHRINKFILE provides more flexibility than DBCC SHRINKDATABASE because you can use it on a single database file without affecting other files that belong to the same database. DBCC SHRINKFILE receives the target size parameter, which is the desired final size for the database file.

use tempdb

dbcc shrinkfile (tempdev, 'target size in MB')

dbcc shrinkfile (templog, 'target size in MB')

 

An advantage of DBCC SHRINKFILE is that it can reduce the size of a file to a size smaller than its original size. You can issue DBCC SHRINKFILE on any of the data or log files. A limitation of DBCC SHRINKFILE is that you cannot make the database smaller than the size of the model database.

 

For More details Check out:

http://support.microsoft.com/kb/307487