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:
select (size*8) as FileSizeKB from sys.database_files
SQL Server 2008 is not affected by the problem that the incorrect size of tempdb
files is displayed after a shrink operation.
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
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.
SQL Server Books Online; topics: "DBCC SHRINKFILE"; "DBCC SHRINKDATABASE"