收缩文件Shrink a File

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题介绍如何通过使用 SQL Server 2019 (15.x)SQL Server 2019 (15.x)SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中收缩数据或日志文件。This topic describes how to shrink a data or log file in SQL Server 2019 (15.x)SQL Server 2019 (15.x) by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

收缩数据文件通过将数据页从文件末尾移动到更靠近文件开头的未占用的空间来恢复空间。Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. 在文件末尾创建足够的可用空间后,可以取消对文件末尾的数据页的分配并将它们返回给文件系统。When enough free space is created at the end of the file, data pages at end of the file can be deallocated and returned to the file system.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 主数据文件不能收缩到小于 model 数据库中的主文件的大小。The primary data file cannot be made smaller than the size of the primary file in the model database.

建议Recommendations

  • 被移动用来收缩文件的数据可以分布到文件的任何可用位置。Data that is moved to shrink a file can be scattered to any available location in the file. 这将导致索引碎片并使搜索索引范围的查询变慢。This causes index fragmentation and can slow the performance of queries that search a range of the index. 若要消除碎片,请考虑在收缩后重新生成文件的索引。To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.

SecuritySecurity

权限Permissions

要求具有 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员身份。Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

使用 SQL Server Management StudioUsing SQL Server Management Studio

收缩数据或日志文件To shrink a data or log file

  1. 在对象资源管理器中,连接到 SQL Server 数据库引擎SQL Server Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of the SQL Server 数据库引擎SQL Server Database Engine and then expand that instance.

  2. 展开 “数据库” ,再右键单击要收缩的数据库。Expand Databases and then right-click the database that you want to shrink.

  3. 依次指向 “任务”“收缩” ,再单击 “文件”Point to Tasks, point to Shrink, and then click Files.

    “数据库”Database
    显示所选数据库的名称。Displays the name of the selected database.

    文件类型File type
    选择文件的文件类型。Select the file type for the file. 可用的选项包括 “数据”“日志” 文件。The available choices are Data and Log files. 默认选项为 “数据”The default selection is Data. 选择不同的文件组类型,其他字段中的选项会相应地发生更改。Selecting a different filegroup type changes the selections in the other fields accordingly.

    文件组Filegroup
    在与以上所选的 “文件类型” 相关联的文件组列表中选择文件组。Select a filegroup from the list of Filegroups associated with the selected File type above. 选择不同的文件组,其他字段中的选项会相应地发生更改。Selecting a different filegroup changes the selections in the other fields accordingly.

    文件名File name
    从所选文件组和文件类型的可用文件列表中选择文件。Select a file from the list of available files of the selected filegroup and file type.

    位置Location
    显示当前所选文件的完整路径。Displays the full path to the currently selected file. 此路径无法编辑,但是可以复制到剪贴板。The path is not editable, but it can be copied to the clipboard.

    当前分配的空间Currently allocated space
    对于数据文件,会显示当前分配的空间。For data files, displays the current allocated space. 对于日志文件,会显示根据 DBCC SQLPERF (LOGSPACE) 的输出计算出的当前分配的空间。For log files, displays the current allocated space computed from the output of DBCC SQLPERF(LOGSPACE).

    可用空间Available free space
    对于数据文件,会显示根据 SHOWFILESTATS (fileid) 的输出计算出的当前可用空间。For data files, displays the current available free space computed from the output of DBCC SHOWFILESTATS(fileid). 对于日志文件,会显示根据 DBCC SQLPERF (LOGSPACE) 的输出计算出的当前可用空间。For log files, displays the current available free space computed from the output of DBCC SQLPERF(LOGSPACE).

    释放未使用的空间Release unused space
    将任何文件中未使用的空间释放给操作系统,并将文件收缩到最后分配的区,因此无需移动任何数据即可减小文件尺寸。Cause any unused space in the files to be released to the operating system and shrink the file to the last allocated extent, reducing the file size without moving any data. 不会将行重新定位到未分配的页。No attempt is made to relocate rows to unallocated pages.

    在释放未使用的空间前重新组织页Reorganize pages before releasing unused space
    等效于执行用于指定目标文件大小的 DBCC SHRINKFILE。Equivalent to executing DBCC SHRINKFILE specifying the target file size. 选中此选项时,用户必须在 “将文件收缩到” 框中指定目标文件的大小。When this option is selected, the user must specify a target file size in the Shrink file to box.

    “将文件收缩到”Shrink file to
    为收缩操作指定目标文件的大小。Specifies the target file size for the shrink operation. 此大小值不得小于当前分配的空间或大于为文件分配的全部区的大小。The size cannot be less than the current allocated space or more than the total extents allocated to the file. 如果输入的值超出最小值或最大值,那么一旦焦点改变或单击工具栏上的按钮时,数值将恢复到最小值或最大值。Entering a value beyond the minimum or the maximum will revert to the min or the max once the focus is changed or when any of the buttons on the toolbar are clicked.

    通过将数据迁移到同一文件组中的其他文件来清空文件Empty file by migrating the data to other files in the same filegroup
    从指定文件迁移所有数据。Migrate all data from the specified file. 此选项允许使用 ALTER DATABASE 语句删除文件。This option allows the file to be dropped using the ALTER DATABASE statement. 此选项等效于执行带有 EMPTYFILE 选项的 DBCC SHRINKFILE。This option is equivalent to executing DBCC SHRINKFILE with the EMPTYFILE option.

  4. 选择文件类型和文件名。Select the file type and file name.

  5. 根据需要,选中 “释放未使用的空间” 复选框。Optionally, select the Release unused space check box.

    选中此选项后,将为操作系统释放文件中所有未使用的空间,并将文件收缩到上次分配的区。Selecting this option causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent. 这将减小文件的大小,但不移动任何数据。This reduces the file size without moving any data.

  6. 根据需要,可以选中 “在释放未使用的空间前重新组织文件” 复选框。Optionally, select the Reorganize files before releasing unused space check box. 如果选中此选项,则必须指定 “将文件收缩到” 值。If this is selected, the Shrink file to value must be specified. 默认情况下,该选项为清除状态。By default, the option is cleared.

    选中此选项后,将为操作系统释放文件中所有未使用的空间,并尝试将行重新定位到未分配页。Selecting this option causes any unused space in the file to be released to the operating system and tries to relocate rows to unallocated pages.

  7. 根据需要,输入在收缩数据库后数据库文件中要保留的最大可用空间百分比。Optionally, enter the maximum percentage of free space to be left in the database file after the database has been shrunk. 值可以介于 0 和 99 之间。Permissible values are between 0 and 99. 只有启用 “在释放未使用的空间前重新组织文件” 以后,此选项才可用。This option is only available when Reorganize files before releasing unused space is enabled.

  8. 根据需要,选中 “通过将数据迁移到同一文件组中的其他文件来清空文件” 复选框。Optionally, select the Empty file by migrating the data to other files in the same filegroup check box.

    选中此选项后,将指定文件中的所有数据移至同一文件组中的其他文件中。Selecting this option moves all data from the specified file to other files in the filegroup. 然后就可以删除空文件。The empty file can then be deleted. 此选项与执行包含 EMPTYFILE 选项 DBCC SHRINKFILE 相同。This option is the same as executing DBCC SHRINKFILE with the EMPTYFILE option.

  9. 单击“确定” 。Click OK.

使用 Transact-SQLUsing Transact-SQL

收缩数据或日志文件To shrink a data or log file

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例使用 DBCC SHRINKFILEUserDB 数据库中名为 DataFile1 的数据文件的大小收缩到 7 MB。This example uses DBCC SHRINKFILE to shrink the size of a data file named DataFile1 in the UserDB database to 7 MB.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

另请参阅See Also

DBCC SHRINKDATABASE (Transact-SQL) DBCC SHRINKDATABASE (Transact-SQL)
收缩数据库 Shrink a Database
删除数据库中的数据文件或日志文件 Delete Data or Log Files from a Database
sys.databases (Transact-SQL) sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)sys.database_files (Transact-SQL)