壓縮檔案Shrink a File

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

此主題描述如何使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management Studio ,在 Transact-SQLTransact-SQL中壓縮資料或記錄檔。This topic describes how to shrink a data or log file in SQL Server 2017SQL Server 2017 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 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.

安全性Security

PermissionsPermissions

需要 系統管理員 固定伺服器角色或 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 Database EngineSQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database EngineSQL 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
    針對資料檔,顯示從 DBCC 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 EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 下列範例會使用 DBCC SHRINKFILE ,將 UserDB 資料庫中名為 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)