??? SQL Server ??????? tempdb ?? ???? ???? ?? ??? ???? ????

?? ???? ??? ??????? ??? ???? ???? ?? ??? ????? ?? ???? ??? ?? ????? ???? ??tempdb??????? ???????? ???? ????? ???? ?? ???? ???? ?? ??? ??? ???? ???? ???? ??? ?? ???? ?? ???? ????????tempdb??????? ?? ????? ??? SQL ????? ?? ???????? ???? ?? ??? ??????? ????? ???? shrinkstempdb??? ?????? ?? ???, ?? ????? ?? ??? ??? ?????? ????? ?? ???? ??? SQL Server ???????? ????? ????? ?????? ??? ???-??? ??????? ?? ???? ???? ?? ??? ???? ???? ??tempdb. ????? ?? ??????? ?? ???????? ?? ?? ??? ?? ??????? ??? ??tempdb??????? shrink ???????? ?? ????? ???

???:??? ?? SQL Server 2005 ?? ????? ?? ??? ???, ?? ????? ??????? ??? ?? ???? ??? ?? ??? ??, ?? ????? ???? ????? SQL Server ??????? Studio ?????????? ??????? ?? ?????? ???????? ?? ????? ?? ?? ???????? ???? ?? ???? ??? ??? ???? ?? SQL ????? ??????? ???????? ??? 2005 ???? ?????? tempdb ??????? ?? ??? ???? ?? shrink ???????? ???? ?? ???? '??????? ??? ?????? ?????' ??? ?? sys.master_files DMV pulled ????? ??, ?? ?? ??? ?????? ???? ?? ??? tempdb ??????? ?? ??? ??? shrink ???????? ???? ???? ??? Tempdb ?? ??? ???? ?? ?????? ?? ??? ??? shrink ???????? ???? ?? ??? ??????? ??????? ???????? ??? ????? ??? ?? ????????? ????:
???:SQL Server 2008 is not affected by this problem (GUI showing incorrect size.)

Tempdb Information

Thetempdbis a temporary workspace. Among other uses, SQL Server uses thetempdbfor:

• 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. ???? ?????, ?? ??? ?? ?? SQL Server ???????? tempdb re-created ?? ??????? ??? ????????? ??? ?? ???? ?? ??? ??????? ??? ???? ?? ???????? ???? ??; ?????, ?? ???? ?? ??? ????????? ?? ??? ??? ?? ?????? ???? ?? ??? ?????? ????? SQL ????? ??????? ???? ??, ?? tempdb ???? ??????? ?? ?? ????????? ?? ????? ?? ???: ???? ?? ?? ???????? ???? ????? ???? ?? ????? ???? ???

???????? ??? ??, tempdb ??????? ?? ???????? ???? ?? ??? autogrow ?????????????; ?????, ?? ??????? ?? ???? ?? ????? ??? ??? ???? ???? ?? ??? ?????? ?? ???? ??? SQL ????? ?? ??? ?????? ???????? ???? ?? ??? ???????? ???? ??? ???? ????? ???? tempdb ?? ???? ?? ????? ???? ??? ???????? ???? ??? ???? ??????? FILE ?????? ?? DBCC SHRINKFILE ??? ?? ??? ???? ???????? ??????? ???????? ????? ???? ???? ?? ??? ??? ????? ?????? ???? ??? ?? ???? presents ??? ??????? tempdb ?? ???? ???????? ???? ?? ???? ???? ???? ???? ?? ??? ????? ?? ???? ????

???? 1 Tempdb ?? ???? ???? ?? ???

?? ???? SQL ????? ?? ???????????? ???? ?? ???????? ???

1. SQL ????? ??? ????? ?? ????? ????????? ?????, ?? ???? ??? ????? ??????? ?? ???? ???? SQL ????? ?? ??????? ????:
   
   sqlservr -c -f
   
   The-c, ??-fparameters cause SQL Server to start in a minimum configuration mode with atempdbsize of 1 MB for the data file and 0.5 MB for the log file.
   
   ???:: 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-sswitch (-s%instance_name%).
2. Connect to SQL Server with 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
   					

3. Stop SQL Server by pressingCtrl-Cat 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 defaulttempdblogical files,tempdev, ??templog. If additional files were added totempdbyou can shrink them after you restart SQL Server as a service. ???tempdbfiles are re-created during startup; therefore, they are empty and can be removed. To remove additional files intempdb, use the ALTER DATABASE command with the REMOVE FILE option.

Method 2 to Shrink Tempdb

Use the DBCC SHRINKDATABASE command to shrink thetempdbdatabase as a whole. DBCC SHRINKDATABASE receives the parametertarget_percent, which 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.

??????????: If you run DBCC SHRINKDATABASE, no other activity can be occurring with thetempdb???????:. To make sure that other processes cannot usetempdbwhile DBCC SHRINKDATABASE is run, you must start SQL Server in single user mode. For more information refer to theEffects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use?? ???? ?? ????

1. Determine the space currently used intempdbby using thesp_spaceused???????? ?????????? Then, calculate the percentage of free space left for use as a parameter to DBCC SHRINKDATABASE; this calculation is based on the desired database size.
   
   ???:In some cases you may have to executesp_spaceused @updateusage=trueto recalculate the space used and to obtain an updated report. Refer to SQL Server Books Online for more information about thesp_spaceused???????? ??????????
   
   Consider this example:
   Assume thattempdbhas two files, the primary data file (Tempdb.mdf), which is 100 MB in size and the log file (Tempdb.ldf), which is 30 MB. Assume thatsp_spaceusedreports 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% and that is yourtarget_percent. The transaction log file is shrunk accordingly, leaving 25% or 20 MB of space free after the database is shrunk.
2. Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL commands:

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

There are limitations for use of the DBCC SHRINKDATABASE command on thetempdb???????:. The target size for data and log files cannot be smaller than the size specified when the database was created or the last size explicitly set with a file-size changing operation such as ALTER DATABASE with the MODIFY FILE option or the DBCC SHRINKFILE command. Another limitation of DBCC SHRINKDATABASE is the calculation of thetarget_percentageparameter and its dependency on the current space used.

Method 3 to Shrink Tempdb

Use the command DBCC SHRINKFILE to shrink the individualtempdb???????? 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 thetarget sizeparameter, which is the desired final size for the database file.

??????????: You must run DBCC SHRINKFILE command while no other activity occurs in thetempdb???????:. To make sure that other processes cannot usetempdbwhile DBCC SHRINKFILE executes, you must restart SQL Server in the single user mode. For more information about DBCC SHRINKFILE, see theEffects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use?? ???? ?? ????

1. Determine the desired size for the primary data file (tempdb.mdf), the log file (templog.ldf), and/or additional files added totempdb. Make sure that the space used in the files is less than or equal to the desired target size.
2. Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL commands for the specific database files that you need 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, look at the last paragraph.
   						

An advantage of DBCC SHRINKFILE is that it can reduce the size of a file to a size 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. However, by default,tempdbhas thetrunc log on chkpt???? ?????? ??? ????; ?????, ???? ???? ???? ?? ??????? ?? ??? ???? ??? truncation ????? ???? ?? ??? ??????????? ??????? ?? ??? ???? ???? ??????? ?? ???? ???? ?? ??? ????????? ??? SQL Server 7.0 ???, ????? ???? ?????? ????? ???? ?? Microsoft ???????? ??? ?????:

DBCC SHRINKDATABASE ?? ??? ????? ??? ?? Tempdb DBCCSHRINKFILE ?? ???????? ?? ??????

???tempdb????? ??? ?? ???? DBCC SHRINKDATABASE ?? ????? ???? ?? ?????? ???? ??? ?? DBCC SHRINKFILE ????, ?????? ?????? ????????? ????? ?????? ?? ???? ??????? ?? ???? ?? ?? shrink ???????? ???? ?? ???? ??:?????? ?????? 2501 ??? ???? ?? ???? ?? indicative ? ??tempdb, ???? ???? shrink ???????? ????? ?? ????? ???, ?????? 8909 ??? ???? ????? ?? ???tempdb???????:. SQL ????? re-create ???? ?? ??? ???????? ????tempdb?? ??????? ????????? ?? ??? ????? ???????, ?? ???? ?? ????? ???? ????? ????????? ?? ??? ???? ?????? ?? ???? ?????? 8909 ?? ?? ???????? ?? ?????/?????? ???????? ????? ????? ??? ?????

??????

SQL ????? ???????? ??????; ????: "DBCC SHRINKFILE"; "DBCC SHRINKDATABASE"