How to shrink the tempdb database in SQL Server

This article discusses three methods you can use to shrink the tempdb database to a size smaller than its last configured size. The first method gives you complete control of the size of the tempdb files but it requires you to restart SQL Server. The second method shrinks the tempdb as a whole, with some limitations, which may include restarting SQL Server. The third method allows you to shrink individual files in the tempdb. The last two methods require that no activity occur in the tempdb database during the shrink operation.

Note If you are using SQL Server 2005, these methods also apply. However, you should use SQL Server Management Studio instead of Enterprise Manager and Query Analyzer to perform these operations. Further note that the SQL Server management studio in 2005 does not show the correct size of tempdb files after a shrink operation. The 'Currently allocated space' value is always pulled from sys.master_files DMV and this value is not updated after a shrink operation happens for tempdb database. To find the correct size of tempdb files after a shrink operation execute the following statement in management studio:
Note SQL Server 2008 is not affected by this problem (GUI showing incorrect size.)

Tempdb Information

The tempdb is a temporary workspace. Among other uses, SQL Server uses the tempdb for:

• Storage of explicitly created temporary tables.
• Worktables that hold intermediate results created during query processing and sorting.
• Materialized static cursors.

SQL Server records only enough information in the tempdb transaction log to roll back a transaction but not to redo transactions during database recovery. This feature increases the performance of INSERT statements in the tempdb. In addition, there is no need to log information to redo any transactions because the tempdb is re-created every time you restart SQL Server; therefore, it does not have any transactions to roll forward or roll back. When SQL Server starts, the tempdb is re-created by using a copy of the model database and is reset to its last configured size.

By default, the tempdb database is configured to autogrow as needed; therefore, this database may grow in time to a size larger than desired. A simple restart of SQL Server resets the size of tempdb to its last configured size. The configured size is the last explicit size set with a file size changing operation such as ALTER DATABASE with the MODIFY FILE option or the DBCC SHRINKFILE statement. This article presents three methods you can use to shrink tempdb to a size smaller than its configured size.

Method 1 to Shrink Tempdb

This method requires you to restart SQL Server.

1. Stop SQL Server. Open a command prompt, and then start SQL Server by typing the following command:
   
   sqlservr -c -f
   
   The -c and -f parameters cause SQL Server to start in a minimum configuration mode with a tempdb size of 1 MB for the data file and 0.5 MB for the log file.
   
   NOTE: If you use a SQL Server named instance, you must change to the appropriate folder (Program Files\Microsoft SQL Server\MSSQL$instance name\Binn) and use the -s switch (-s%instance_name%).
2. Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL commands:

      ALTER DATABASE tempdb MODIFY FILE
      (NAME = 'tempdev', SIZE = target_size_in_MB) 
      --Desired target size for the data file
   
      ALTER DATABASE tempdb MODIFY FILE
      (NAME = 'templog', SIZE = target_size_in_MB)
      --Desired target size for the log file
   					

3. Stop SQL Server by pressing Ctrl-C at the command prompt window, restart SQL Server as a service, and then verify the size of the Tempdb.mdf and Templog.ldf files.

A limitation of this method is that it only operates on the default tempdb logical files, tempdev and templog. If additional files were added to tempdb you can shrink them after you restart SQL Server as a service. All tempdb files are re-created during startup; therefore, they are empty and can be removed. To remove additional files in tempdb, use the ALTER DATABASE command with the REMOVE FILE option.

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.

IMPORTANT: If you run DBCC SHRINKDATABASE, no other activity can be occurring with the tempdb database. To make sure that other processes cannot use tempdb while DBCC SHRINKDATABASE is run, you must start SQL Server in single user mode. For more information refer to the Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use section of this article.

1. Determine the space currently used in tempdb by using the sp_spaceused stored procedure. Then, calculate the percentage of free space left for use as a parameter to DBCC SHRINKDATABASE; this calculation is based on the desired database size.
   
   Note In some cases you may have to execute sp_spaceused @updateusage=true to recalculate the space used and to obtain an updated report. Refer to SQL Server Books Online for more information about the sp_spaceused stored procedure.
   
   Consider this example:
   Assume that tempdb has two files, the primary data file (Tempdb.mdf), which is 100 MB in size and the log file (Tempdb.ldf), which is 30 MB. Assume that sp_spaceused reports that the primary data file contains 60 MB of data. Also assume that you want to shrink the primary data file to 80 MB. Calculate the desired percentage of free space left after the shrink, 80 MB - 60 MB = 20 MB. Now, divide 20 MB by 80 MB = 25% and that is your target_percent. The transaction log file is shrunk accordingly, leaving 25% or 20 MB of space free after the database is shrunk.
2. Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL commands:

      dbcc shrinkdatabase (tempdb, 'target percent') 
      -- This command shrinks the tempdb database as a whole
   					

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.

IMPORTANT: You must run DBCC SHRINKFILE command while no other activity occurs in the tempdb database. To make sure that other processes cannot use tempdb while DBCC SHRINKFILE executes, you must restart SQL Server in the single user mode. For more information about DBCC SHRINKFILE, see the Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use section of this article.

1. Determine the desired size for the primary data file (tempdb.mdf), the log file (templog.ldf), and/or additional files added to tempdb. Make sure that the space used in the files is less than or equal to the desired target size.
2. Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL commands for the specific database files that you need to shrink:

      use tempdb
      go
   
      dbcc shrinkfile (tempdev, 'target size in MB')
      go
      -- this command shrinks the primary data file
   
      dbcc shrinkfile (templog, 'target size in MB')
      go
      -- this command shrinks the log file, look at the last paragraph.
   						

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.

In SQL Server 7.0 a transaction log shrink is a deferred operation and you must issue a log truncation and backup to help the shrink operation in a database. However, by default, tempdb has the trunc log on chkpt option set ON; therefore, there you do not need to issue a log truncation for that database. For additional information how to shrink a database transaction log in SQL Server 7.0, click the article number below to view the article in the Microsoft Knowledge Base:

Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use

If tempdb is in use and you attempt to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, you may receive multiple consistency errors similar to the following type and the shrink operation may fail: Although error 2501 may not be indicative of any corruption in tempdb, it causes the shrink operation to fail. On the other hand, error 8909 could indicate corruption in the tempdb database. Restart SQL Server to re-create tempdb and clean up the consistency errors. However, keep in mind that there could be other reasons for physical data corruption errors like error 8909 and those include input/output subsystem problems.

REFERENCES

SQL Server Books Online; topics: "DBCC SHRINKFILE"; "DBCC SHRINKDATABASE"