Article ID: 307487 - View products that this article applies to.
This article was previously published under Q307487
Expand all | Collapse all

On This Page

Summary

This article discusses three methods that you can use to shrink the tempdb database to a size that is smaller than its last configured size. The first method gives you complete control of the size of the tempdb files. However, the first method requires you to restart Microsoft SQL Server. The second method shrinks the tempdb database, and you may have to restart SQL Server. The third method lets you shrink individual files in the tempdb database. The last two methods require that no activity can 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. Also be aware that the 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 tempdb
select (size*8) as FileSizeKB from sys.database_files

Note SQL Server 2008 is not affected by the problem that the incorrect size of tempdb files is displayed after a shrink operation. 

Tempdb information

The tempdb is a temporary workspace. Among other uses, SQL Server uses the tempdb for the following:
  • 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, you do not have to log information to redo any transactions because the 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, the tempdb is re-created by using a copy of the model database, and the tempdb 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 the desired size. A simple restart of SQL Server resets the size of the tempdb 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 statement.

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

Method 2: Use the DBCC SHRINKDATABASE command

Method 3: Use the DBCC SHRINKFILE command

Effects of execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE while tempdb is in use

If tempdb is being used, and you try to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, you may receive multiple consistency errors that resemble the following, and the shrink operation may fail:
  • 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.
Although error 2501 may not indicate any corruption in tempdb, this error 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, be aware that there might 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"

Properties

Article ID: 307487 - Last Review: July 12, 2013 - Revision: 9.1
Applies to
  • 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
Keywords: 
kbsqlsetup kbhowtomaster KB307487

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com