Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
How to shrink the tempdb database in SQL Server
Article ID: 307487 - View products that this article applies to.
This article was previously published under Q307487
NoticeIf you are a small business customer, you can find additional troubleshooting and learning resources at the Support for Small Business
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:
Note SQL Server 2008 is not affected by the problem that the incorrect size of tempdb files is displayed after a shrink operation.
Tempdb informationThe tempdb is a temporary workspace. Among other uses, SQL Server uses the tempdb for the following:
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 useIf 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:
REFERENCESSQL Server Books Online; topics: "DBCC SHRINKFILE"; "DBCC SHRINKDATABASE"
Article ID: 307487 - Last Review: July 20, 2012 - Revision: 9.0