How to contract the tempdb database in SQL Server

From: http://support.microsoft.com/kb/307487/zh-cn

This article discusses three methods to shrink the tempdb database to a size smaller than the size configured last time. The first method allows you to fully control the size of the tempdb file, but it requires you to restart SQL Server. The second method compresses tempdb as a whole, but it has some restrictions, including restarting SQL Server. The third method allows you to shrink a single file in tempdb. The last two methods require no activity in the tempdb database during the contraction operation.

Note: If you are using SQL Server 2005, these methods still apply. However, you should use SQL Server Management studio instead of the Enterprise Manager and query analyzer to perform these operations.

 

Tempdb Information

TempdbIs a temporary workspace. In addition to other functions, SQL Server alsoTempdbUsed:

• The storage of temporary tables that are explicitly created.
• The worksheet that saves the intermediate results created during Query Processing and sorting.
• A specific static cursor.

SQL Server records only enough information in the tempdb transaction log to roll back the transaction, not enough to re-execute the transaction in the database fault recovery process. This feature improves the performance of insert statements in tempdb. In addition, since tempdb is re-created every time you restart SQL Server, you do not need to record the information used to re-execute any transaction. Therefore, there are no transactions to be rolled back or rolled back. When SQL Server is started, use the copy of the model database to recreate tempdb and reset it to the size configured last time.

By default, the tempdb database is configured to automatically grow as needed. Therefore, the database may eventually grow to a size greater than the required size. Simply restarting SQL server will reset the size of tempdb TO THE SIZE configured last time. The configured size is the last explicit size set by the file size change operation (such as the alter database or DBCC shrinkfile statement with the modify file option. This article describes three methods you can use to contract tempdb to a size smaller than the configured size.

Method 1 for shrinking tempdb

This method requires you to restart SQL Server.

1. stop SQL Server. Open a command prompt and enter the following command to start SQL Server:

   sqlservr-C-F

   -C and -F parameters enable SQL Server to start in the minimum configuration mode, set the tempdb size of the data file to 1 MB, and the log file's tempdb to 0.5 Mb.

   note : If you use SQL Server to name an instance, you must switch to the appropriate folder ( Program Files \ Microsoft SQL Server \ MSSQL $ Instance name \ binn ), use the -S switch (-S % instance_name % ).

2. connect to SQL Server with the query analyzer and run the following Transact-SQL command:

    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 

3. stop SQL Server by pressing Ctrl-C in the Command Prompt window, restart SQL server as a service, and then verify tempdb. MDF and templog. the size of the LDF file.

The limitation of this method is that it can onlyTempdbLogical FileTempdevAndTemplog. If you add other filesTempdbYou can contract SQL Server after it is restarted as a service. AllTempdbFiles; therefore, they are empty and can be deleted. To deleteTempdbFor other files in, use the alter database command with the Remove File option.

Demo:

Alter database tempdb Modify file (name = 'tempdev', size = 8) -- specify 8 m
Alter database tempdb Modify file (name = 'templog', size = 1) -- 1 m
Alter database tempdb Remove File tempdev1 -- delete the tempdev1 file at the same time
Select * From sysaltfiles s -- View database files in ms SQL 2000

 

Method 2 for shrinking tempdb

Run the DBCC shrinkdatabase commandTempdbThe database is reduced as a whole. DBCC shrinkdatabase receiving ParametersTarget_percentThis parameter is the percentage of available space in the database file after the database shrinks. If you use DBCC shrinkdatabase, you may have to restart SQL Server.

Important: If you run DBCC shrinkdatabaseTempdbThe database cannot have other activities. Make sure that other processes are unavailable when running DBCC shrinkdatabase.TempdbYou must start SQL Server in single-user mode. For more information, refer toExecute the result of DBCC shrinkdatabase or dbccshrinkfile when using tempdb.

1. use the sp_spaceused stored procedure to determine the space currently used in tempdb . Then, calculate the percentage of available space, which will be used as the parameter of DBCC shrinkdatabase. The calculation is based on the size of the required database.

   note : in some cases, you may have to execute sp_spaceused @ updateusage = true to recalculate the space used and obtain the updated report. For more information about the sp_spaceused stored procedure, see SQL Server books online.

   consider the following example:

   assume that tempdb has two files: master data file (tempdb. MDF) and log files (tempdb. LDF), which is 100 mb and 30 mb respectively. Assume that sp_spaceused reports that the master data file contains 60 MB data. It is also assumed that you want to contract the master data file to 80 Mb. Calculate the percentage of available space after contraction, that is, 80 Mb-60 MB = 20 mb. Now, divide 20 mb by 80 Mb = 25%, which is your target_percent . The transaction log file will be reduced accordingly, so that the remaining 25% MB of available space after the database contraction.
2. connect to SQL Server with the query analyzer and run the following Transact-SQL command:

    DBCC shrinkdatabase (tempdb, 'target percent ') -- this command shrinks the tempdb database as a whole 

PairTempdbThe database has limitations when using the DBCC shrinkdatabase command. The target size of the data file and log file cannot be smaller than the size specified when the database is created, or be smaller than the size of the file to be changed (for example, the alter database command with the modify file option or the DBCC shrinkfile command) the size of the last time explicitly set. Another limitation of DBCC shrinkdatabase isTarget_percentageParameter Calculation and its dependency on the space currently used.

Method 3 for shrinking tempdb

Use the command DBCC shrinkfile to contract a singleTempdbFile. DBCC shrinkfile provides more flexibility than DBCC shrinkdatabase because it can be used for a single database file without affecting other files belonging to the same database. Receive DBCC shrinkfileTarget sizeParameter, which is the final size of the required database file.

Important: Must be inTempdbThe DBCC shrinkfile command is run when no activity occurs in the database. Make sure that other processes are unavailable when DBCC shrinkfile is executed.TempdbYou must restart SQL Server in single-user mode. For more information about DBCC shrinkfile, seeExecute the result of DBCC shrinkdatabase or dbccshrinkfile when using tempdb.

1. Determine the master data file (tempdb. MDF), log file (templog. LDF), and/or addTempdbThe size of other files. Make sure that the space used in these files is smaller than or equal to the required target size.
2. Connect to SQL Server with the query analyzer, and then run the following Transact-SQL command for the specific database to be shrunk:

   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, look at the last paragraph.

One advantage of DBCC shrinkfile is that it can reduce the file size to smaller than its original size. You can run DBCC shrinkfile on any data file or log file. One limitation of DBCC shrinkfile is that you cannot make the database smaller than the size of the model database.

In SQL Server 7.0, transaction log shrinking is a deferred operation. You must perform log truncation and backup to help with database shrinking. However, by default,TempdbSetTrunc log on chkptYou do not need to perform log Truncation for the database. For more information about how to shrink Database Transaction logs in SQL Server 7.0, clickArticleTo view the articles in the Microsoft Knowledge Base:

256650 inf: How to contract SQL Server 7.0 transaction logs

 

Execute the result of DBCC shrinkdatabase or dbccshrinkfile when using tempdb

When usingTempdbIf you try to contract it by using the DBCC shrinkdatabase or DBCC shrinkfile command, you may receive multiple consistency errors similar to the following types, and the contraction operation may fail:

Server: MSG 2501, level 16, state 1, line 1 cocould not find table named '123 '. check sysobjects. -Or-server: MSG 8909, level 16, state 1, line 0 table upload upt: Object ID 1, index ID 0, page ID % s_pgid.the pageid in the page header = % s_pgid.

Error 2501 may not indicateTempdbBut it will cause the contraction operation to fail. In contrast, error 8909 may indicateTempdbDatabase corruption. SQL Server should be restarted to recreateTempdbAnd clear consistency errors. However, remember that physical data corruption such as error 8909 may be caused by other reasons, including input/output subsystem problems.

Reference

SQL Server books online; topic: "DBCC shrinkfile"; "DBCC shrinkdatabase"