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.
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
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.
Collapse this tableExpand this table
|Method||Requires restart?||More information|
|Yes||Gives complete control on the size of the default tempdb files (tempdev and templog). |
|No||Operates at database level.|
|No||Lets you shrink individual files.|
|SQL Server Management Studio|
|No||This is basically a way to shrink database files through the GUI.|
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
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
Method 2: Use the DBCC SHRINKDATABASE command
Method 3: Use the DBCC SHRINKFILE command
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
SQL Server 2000
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).
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.
- 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:
Error message "Table Corrupt Object ID 0, index ID 0, page ID" occurs when you run DBCC DBREINDEX in SQL Server
Shrink a Database
Article ID: 307487 - Last Review: April 30, 2014 - Revision: 10.3
- 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 Parallel Data Warehouse
- Microsoft SQL Server 2012 Standard
- Microsoft SQL Server 2012 Web
|kbsqlsetup kbhowtomaster KB307487|