How to shrink the tempdb database in SQL Server

This article discusses various methods that you can use to shrink the tempdb database in Microsoft SQL Server. Before you shrink the tempdb database by using the methods that are described in this article, please be aware of the following:

• The tempdb size is reset to the last configured size (that is, to the default size, or the last size that was set by using alter database) after each restart. Therefore, unless you have to use different values or obtain immediate respite, you do not have to use any of the procedures that are documented in this article. You can wait for the next restart of the SQL Server service for the size to decrease. Larger tempdb database sizes will not adversely affect the performance of SQL Server.
• In SQL Server 2005 and later versions, shrinking the tempdb database is no different than shrinking a user database except for the fact that tempdb resets to its configured size after each restart of the instance of SQL Server.
• It is safe to run shrink in tempdb while tempdb activity is ongoing. However, you may encounter other errors such as blocking, deadlocks, and so on that can prevent shrink from completing. Therefore, in order to make sure that a shrink of tempdb will succeed, we recommend that you do this while the server is in single-user mode or when you have stopped all tempdb activity.

Tempdb information

The tempdb database is a temporary workspace. SQL Server uses tempdb to perform many tasks. Among those tasks are the following:

• Storage of explicitly created temporary tables
• Worktables that hold intermediate results that are created during query processing and sorting
• Materialized static cursors
• Storage of version records when snapshot isolation levels or read-committed snapshot isolation levels are used

use tempdb
go






select (size*8) as FileSizeKB from sys.database_files

The first three methods are discussed here.

Note For SQL Server 2000 installations, you will have to use Query Analyzer instead of SQL Server Management Studio. Also, the DBCC commands will require you to put the database in single-user mode.

You can use the following three methods to shrink tempdb to a size that is smaller than its configured size.

Method 1: Use Transact-SQL commands
Note This method requires you to restart SQL Server.

1. Stop SQL Server.
2. At a command prompt, start the instance in minimum configuration mode. To do this, follow these steps:
   1. At a command prompt, change to the following folder:
   2. If the instance is a named instance of SQL Server, run the following command:
      sqlservr.exe -s InstanceName -c -f
      If the instance is the default instance of SQL Server, run the following command:
      
      sqlservr -c -f
      Note The -c and -f parameters cause SQL Server to start in a minimum configuration mode that has a tempdb size of 1 MB for the data file and 0.5 MB for the log file.
3. Connect to SQL Server by using 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
   

4. Stop SQL Server. To do this, press 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.

1. Determine the space that is currently used in tempdb by using the sp_spaceused stored procedure. Then, calculate the percentage of free space that is 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 that is used and to obtain an updated report. Refer to SQL Server Books Online for more information about the sp_spaceused stored procedure.
   
   
   Consider the following example:
   Assume that tempdb has two files: the primary data file (Tempdb.mdf) that is 100 MB and the log file (Tempdb.ldf) that 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 percent, and that is yourtarget_percent. The transaction log file is shrunk accordingly, leaving 25 percent or 20 MB of space free after the database is shrunk.
2. Connect to SQL Server by using Query Analyzer, and then run the following Transact-SQL commands:

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

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 that is specified when the database was created or smaller than the last size that was explicitly set by using a file-size-changing operation such as ALTER DATABASE that uses the MODIFY FILE option or the command. Another limitation of BCC SHRINKDATABASE is the calculation of the target_percentage parameter and its dependency on the current space that is used.

Method 3: Use the DBCC SHRINKFILE command
Use the DBCC SHRINKFILE command 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. This is the desired final size for the database file.

1. Determine the desired size for the primary data file (tempdb.mdf), the log file (templog.ldf), and additional files that are added to tempdb. Make sure that the space that is used in the files is less than or equal to the desired target size.
2. Connect to SQL Server by using Query Analyzer, and then run the following Transact-SQL commands for the specific database files that you want 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, examine the last paragraph.
   

2501 and 8909 errors when you run shrink operations

• These errors do not indicate any real corruption in tempdb. However, be aware that there might be other reasons for physical data corruption errors like error 8909 and that those reasons include I/O subsystem problems. Therefore, if the error happens outside shrink operations, you should do more investigation.
• In SQL Server 2005 and later versions, although an 8909 message is returned to the application or to the user who is executing the shrink operation, the shrink operations will not fail.
• In SQL Server 2000 and earlier versions, these errors will cause the shrink operations to fail. Therefore to shrink the tempdb database, you will have to restart SQL Server to re-create tempdb.