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
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
Note This method requires you to restart SQL Server.
- Stop SQL Server. At a command prompt, type the following command to start SQL Server:
sqlservr -c -f
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.
Note If you use a SQL Server named instance, you must change to the appropriate folder (Program Files\Microsoft SQL Server\MSSQL$instance name\Binn) and use the -s switch (-s%instance_name%).
- 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
-
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 only operates 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.
Important If you run DBCC SHRINKDATABASE, no other activity can be occurring with the
tempdb database. To make sure that other processes cannot use
tempdb while DBCC SHRINKDATABASE is run, you must start SQL Server in single-user mode. For more information, see the "Effects of execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE while tempdb is in use" section.
-
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 your target_percent. The transaction log file is shrunk accordingly, leaving 25 percent or 20 MB of space free after the database is shrunk.
- 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 DBCC SHRINKFILE command. Another limitation of DBCC 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, and this is the desired final size for the database file.
Important You must run DBCC SHRINKFILE command while no other activity occurs in the
tempdb database. To make sure that other processes cannot use
tempdb while DBCC SHRINKFILE executes, you must restart SQL Server in the single-user mode. For more information about DBCC SHRINKFILE, see the "Effects of execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE while tempdb is in use" section.
- 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.
- 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.
In SQL Server 7.0 a transaction log shrink is a deferred operation, and you must issue a log truncation and backup to help the shrink operation in a database. By default,
tempdb has the
trunc log on chkpt option set to ON. Therefore, you do not have to issue a log truncation for that database.
For more information about how to shrink a database transaction log in SQL Server 7.0, click the following article
to view the article in the Microsoft Knowledge Base:
256650
(http://support.microsoft.com/kb/256650/EN-US/
)
INF: How to Shrink the SQL Server 7.0 Transaction Log
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"
Back to the top
