How to shrink the tempdb database in SQL Server

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Summary
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
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 tempdb. Additionally, you do not have to log information to redo any transactions because tempdb is re-created every time that you restart SQL Server. Therefore, it has no transactions to roll forward or to roll back. When SQL Server starts, tempdb is re-created by using a copy of the model database, and tempdb is reset to its last configured size. The configured size is the last explicit size that was set by using a file size changing operation such as ALTER DATABASE that uses the MODIFY FILE option or the DBCC SHRINKFILE or DBCC SHRINKDATABASE statements.

By default, the tempdb database is configured to autogrow as needed. Therefore, this database may unexpectedly grow in time to a size larger than the desired size. A simple restart of SQL Server resets the size of tempdb to its last configured size.

In SQL Server 2005 and later versions, you can use any of the following methods to alter the size of tempdb.

MethodRequires restart?More information
ALTER DATABASEYesGives complete control on the size of the default tempdb files (tempdev and templog).
DBCC SHRINKDATABASENoOperates at database level.
DBCC SHRINKFILENoLets you shrink individual files.
SQL Server Management StudioNoThis is basically a way to shrink database files through the GUI.

Note SQL Server Management Studio in SQL Server 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 occurs for the tempdb database. To find the correct size of tempdb files after a shrink operation, execute the following statement in SQL Server Management Studio:

use tempdbgoselect (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.
A limitation of this method is that it operates only on the default tempdb logical files tempdev and templog. If more files are added to tempdb, you can shrink them after you restart SQL Server as a service. All tempdb files are re-created during startup. However, they are empty and can be removed. To remove additional files in tempdb, use the ALTER DATABASE command by using the REMOVE FILE option.

Method 2: Use the DBCC SHRINKDATABASE command
Use the DBCC SHRINKDATABASE command to shrink the tempdb database. DBCC SHRINKDATABASE receives the parameter target_percent. This 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.
  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.						
An advantage of DBCC SHRINKFILE is that it can reduce the size of a file to a size that is 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.

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.


2501 and 8909 errors when you run shrink operations

If tempdb is being used, and if you try to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE command, you may receive messages that resemble the following, depending on the version of SQL Server that you are using:

SQL Server 2005 and later versions

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (6:8040) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).

SQL Server 2000

Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.
Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.
Notes
  • 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. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
    277848 Error message "Table Corrupt Object ID 0, index ID 0, page ID" occurs when you run DBCC DBREINDEX in SQL Server


Properties

Article ID: 307487 - Last Review: 04/25/2016 15:54:00 - Revision: 11.0

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 64-bit Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Parallel Data Warehouse, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Web, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Web, Microsoft SQL Server 2008 Workgroup, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Web, Microsoft SQL Server 2014 Business Intelligence, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Web, Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Enterprise Core, Microsoft SQL Server 2016 Standard

  • kbsqlsetup kbhowtomaster KB307487
Feedback