Three Methods to Shrink Tempdb System Database and Files

-->

Tempdb database is a system database; available for all the users connected to the SQL server instances. Database administrator can not perform backup & restore operation on the tempdb database because it is automatically dropped when system is shut down. All the operations in this database are minimally logged and every time SQL server starts with a clean copy of tempdb database. Size of tempdb database can affect the performance of your SQL server database so it is the best practice to shrink tempdb database whenever you see rapidly growth in the size of tempdb database or tempdb transaction log file. Here I will explain top three methods to shrink tempdb database.

1. Shrink Using Transact-SQL Command
2. Shrink Using DBCC SHRINKDATABASE Command
3. Shrink Using DBCC SHRINKFILE Command

1. Shrink using Transact-SQL command: It will provide you complete control over the size of tempdb database. After running T-SQL command you have to restart SQL server database. Perform bellow steps to shrink tempdb:

• Stop SQL server.
• To start SQL server type sqlserver -c -f command on command prompt.
• Now connect to the SQL server using query analyzer & run following T-SQL commands.

ALTER DATABASE tempdb MODIFY FILE

(NAME = 'tempdbdata', SIZE = target_size_in_MB)

ALTER DATABASE tempdb MODIFY FILE

(NAME = 'tempdblog', SIZE = target_size_in_MB)

• Again stop the SQL server.
• Now restart the SQL server as a service and verify the size of tempdbdata.mdf & tempdbdata.ldf files.

2. Shrink using DBCC SHRINKDATABASE Command: It will shrink the tempdb database and you may restart the SQL server. To use this command your database must be in single user mode. If database is not in single user mode then change the mode using Alter command and the shrink command is given bellow:

DBCC Shrinkdatabase (tempdb, 'target percent')

3. Shrink using DBCC SHRINKFILE Command: It will enable you to shrink an individual tempdb database files (.mdf & .ldf) and provides more flexibility than DBCC shrinkdatabase command. As similar to DBCC Shrinkdatabase command your database must be in single user mode to run this command also.

Use tempdb

Go

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

Go

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

Go

Tips: Don't perform any operation on the tempdb database during last two methods as DBCC SHRINKDATABASE & DBCC SHRINKFILE command.