DBCC SHRINKFILE (Transact-SQL)DBCC SHRINKFILE (Transact-SQL)

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database

壓縮目前資料庫的指定資料或記錄檔大小。Shrinks the current database's specified data or log file size. 您可以使用它將資料從某個檔案移至同一檔案群組中的其他檔案,其可清空檔案並允許移除其資料庫。You can use it to move data from one file to other files in the same filegroup, which empties the file and allows for its database removal. 您可以將檔案壓縮成小於其在建立時的大小,將檔案大小下限重設為新值。You can shrink a file to less than its size at creation, resetting the minimum file size to the new value.

文章連結圖示 Transact-SQL 語法慣例Article link icon Transact-SQL Syntax Conventions

語法Syntax

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]  
    }  
)  
[ WITH NO_INFOMSGS ]  

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

引數Arguments

file_namefile_name
所要壓縮檔案的邏輯名稱。The file to be shrunk's logical name.

file_idfile_id
所要壓縮檔案的識別碼 (ID)。The file to be shrunk's identification (ID) number. 若要取得檔案識別碼,請使用 FILE_IDEX 系統函數或在目前的資料庫中查詢 sys.database_files 目錄檢視。To get a file ID, use the FILE_IDEX system function or query the sys.database_files catalog view in the current database.

target_sizetarget_size
整數 - 檔案的新 MB 大小。An integer - the file's new megabyte size. 若未指定,DBCC SHRINKFILE 會縮減成檔案建立大小。If not specified, DBCC SHRINKFILE reduces to the file creation size.

注意

您可以使用 DBCC SHRINKFILE target_size 縮減空白檔案的預設大小。You can reduce an empty file's default size using DBCC SHRINKFILE target_size. 例如,如果建立 5 MB 的檔案,然後再將檔案縮減為 3 MB 且檔案仍維持空白,則預設的檔案大小會設定為 3 MB。For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. 這僅適用於從未包含過資料的空白檔案。This applies only to empty files that have never contained data.

FILESTREAM 檔案群組容器不支援此選項。This option isn't supported for FILESTREAM filegroup containers.
如果已指定,DBCC SHRINKFILE 會嘗試將檔案壓縮成 target_sizeIf specified, DBCC SHRINKFILE tries to shrink the file to target_size. 檔案所要釋出區域中所使用頁面會移至檔案保留區域中的可用空間。Used pages in the file's area to be freed are moved to free space in the file's kept areas. 例如,有 10 MB 的資料檔案時,target_size 為 8 的 DBCC SHRINKFILE 作業會將檔案最後 2 MB 中所有已使用頁面移入檔案前 8 MB 中任何未配置的頁面。For example, with a 10-MB data file, a DBCC SHRINKFILE operation with an 8 target_size moves all used pages in the file's last 2 MB into any unallocated pages in the file's first 8 MB. DBCC SHRINKFILE 不會將檔案壓縮成超過所需的預存資料大小。DBCC SHRINKFILE doesn't shrink a file past the needed stored data size. 例如,如果使用了 10 MB 資料檔案中的 7 MB,將 target_size 設為 6 的 DBCC SHRINKFILE 陳述式,只會將檔案壓縮成 7 MB,而不是 6 MB。For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.

EMPTYFILEEMPTYFILE
將指定檔案中的所有資料移轉到「相同檔案群組」**** 的其他檔案中。Migrates all data from the specified file to other files in the same filegroup. 換言之,EMPTYFILE 會將指定檔案中資料移轉至同一檔案群組中的其他檔案。In other words, EMPTYFILE migrates data from a specified file to other files in the same filegroup. 儘管這不是唯讀檔案,但 EMPTYFILE 可確保不會將任何新資料新增至檔案。EMPTYFILE assures you that no new data gets added to the file, despite this file not being read-only. 您可以使用 ALTER DATABASE 陳述式來移除檔案。You can use the ALTER DATABASE statement to remove a file. 如果您使用 ALTER DATABASE 陳述式來變更檔案大小,則唯讀旗標會重設,且可以新增資料。If you use the ALTER DATABASE statement to change file size, the read-only flag is reset and data can be added.

對於 FILESTREAM 檔案群組容器來說,在 FILESTREAM 記憶體回收行程已執行並刪除所有由 EMPTYFILE 複製至其他容器且已不需要的檔案群組容器檔案之前,您無法使用 ALTER DATABASE 來移除檔案。For FILESTREAM filegroup containers, you can't use ALTER DATABASE to remove a file until the FILESTREAM Garbage Collector has run and deleted all the unnecessary filegroup container files that EMPTYFILE has copied to another container. 如需詳細資訊,請參閱 sp_filestream_force_garbage_collection (Transact-SQL)For more information, see sp_filestream_force_garbage_collection (Transact-SQL)

注意

如需移除 FILESTREAM 容器的詳細資訊,請參閱 ALTER DATABASE 檔案及檔案群組選項 (Transact-SQL)中對應的章節。For information on removing a FILESTREAM container, see the corresponding section in ALTER DATABASE File and Filegroup Options (Transact-SQL)

NOTRUNCATENOTRUNCATE
在有指定或未指定 target_percent 的情況下,將所配置頁面從資料檔案結尾移到檔案前面未配置的頁面。Moves allocated pages from a data file's end to unallocated pages in a file's front with or without specifying target_percent. 檔案結尾的可用空間並不會歸還給作業系統,檔案的實際大小也不會改變。The free space at the file's end isn't returned to the operating system, and the file's physical size does not change. 因此,如果指定了 NOTRUNCATE,檔案不會呈現壓縮狀態。Therefore, if NOTRUNCATE is specified, the file appears not to shrink. NOTRUNCATE 只適用於資料檔案。NOTRUNCATE is applicable only to data files. 記錄檔不受影響。The log files are not affected. FILESTREAM 檔案群組容器不支援此選項。This option isn't supported for FILESTREAM filegroup containers.

TRUNCATEONLYTRUNCATEONLY
將檔案結尾的所有可用空間釋出給作業系統,但是不會在檔案內移動任何頁面。Releases all free space at the file's end to the operating system but does not perform any page movement inside the file. 資料檔案只會壓縮為最後配置的範圍。The data file is shrunk only to the last allocated extent. 如果在使用 TRUNCATEONLY 時指定 target_size,則會予以忽略。target_size is ignored if specified with TRUNCATEONLY.
TRUNCATEONLY 選項不會移動記錄檔中的資訊,但會移除記錄檔結尾之非使用中的 VLF。The TRUNCATEONLY option does not move information in the log, but does remove inactive VLFs from the end of the log file. FILESTREAM 檔案群組容器不支援此選項。This option isn't supported for FILESTREAM filegroup containers.

WITH NO_INFOMSGSWITH NO_INFOMSGS
隱藏所有參考訊息。Suppresses all informational messages.

結果集Result sets

下表會描述結果集資料行。The following table describes result set columns.

資料行名稱Column name 描述Description
DbIdDbId Database EngineDatabase Engine 試圖壓縮之檔案的資料庫識別碼。Database identification number of the file the Database EngineDatabase Engine tried to shrink.
FileIdFileId Database EngineDatabase Engine 試圖壓縮之檔案的檔案識別碼。The file identification number of the file the Database EngineDatabase Engine tried to shrink.
CurrentSizeCurrentSize 檔案目前所佔的 8 KB 頁數。Number of 8-KB pages the file currently occupies.
MinimumSizeMinimumSize 檔案所能佔用的 8 KB 頁數最小值。Number of 8-KB pages the file could occupy, at minimum. 此數字對應於檔案大小下限或最初建立的大小。This number corresponds to the minimum size or originally created size of a file.
UsedPagesUsedPages 檔案目前所用的 8 KB 頁數。Number of 8-KB pages currently used by the file.
EstimatedPagesEstimatedPages Database EngineDatabase Engine 估計檔案可以壓縮成 8 KB 頁面的數目。Number of 8-KB pages that the Database EngineDatabase Engine estimates the file could be shrunk down to.

備註Remarks

DBCC SHRINKFILE 適用於目前資料庫的檔案。DBCC SHRINKFILE applies to the current database's files. 如需有關如何變更目前資料庫的詳細資訊,請參閱 USE (Transact-SQL)For more information about how to change the current database, see USE (Transact-SQL).

您可以隨時停止 DBCC SHRINKFILE 作業,任何已完成的工作都會保留下來。You can stop DBCC SHRINKFILE operations at any point and any completed work is preserved. 如果您使用 EMPTYFILE 參數並取消作業,則不會標示檔案來防止新增額外的資料。If you use the EMPTYFILE parameter and cancel the operation, the file isn't marked to prevent additional data from being added.

當 DBCC SHRINKFILE 作業失敗時,會引發錯誤。When a DBCC SHRINKFILE operation fails, an error is raised.

在檔案壓縮期間,其他使用者可以在資料庫中工作,資料庫不一定要處於單一使用者模式。Other users can work in the database during file shrinking - the database doesn't have to be in single-user mode. 您不需要在單一使用者模式中執行 SQL ServerSQL Server 執行個體來壓縮系統資料庫。You don't have to run the instance of SQL ServerSQL Server in single-user mode to shrink the system databases.

壓縮記錄檔Shrinking a log file

對於記錄檔,Database EngineDatabase Engine 會使用 target_size 來計算整份記錄的目標大小。For log files, the Database EngineDatabase Engine uses target_size to calculate the whole log's target size. 因此,target_size 是壓縮作業之後的記錄檔可用空間。Therefore, target_size is the log's free space after the shrink operation. 之後,便會將整份記錄之目標大小轉換成每個記錄檔的目標大小。The whole log's target size is then translated to each log file's target size. DBCC SHRINKFILE 會試圖將每個實體記錄檔立即壓縮成目標大小。DBCC SHRINKFILE tries to shrink each physical log file to its target size immediately. 不過,如果邏輯記錄有任何部分是在超出目標大小的虛擬記錄中,Database EngineDatabase Engine 盡可能釋出的空間,然後發出一則參考用訊息。However, if part of the logical log resides in the virtual logs beyond the target size, the Database EngineDatabase Engine frees as much space as possible, and then issues an informational message. 這個訊息描述將邏輯記錄移出檔案結尾的虛擬記錄,需要哪些動作。The message describes what actions are required to move the logical log out of the virtual logs at the end of the file. 執行這些動作之後,就可以利用 DBCC SHRINKFILE 來釋出其餘空間。After the actions are performed, DBCC SHRINKFILE can be used to free the remaining space.

由於記錄檔只能壓縮成虛擬記錄檔界限,因此可能無法將記錄檔壓縮成小於虛擬記錄檔的大小,即使它不在使用中也是如此。Because a log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it isn't being used. 建立或擴充記錄檔時,Database EngineDatabase Engine 會以動態方式選擇虛擬記錄檔大小。The Database EngineDatabase Engine dynamically chooses the virtual file log size when log files are created or extended.

最佳作法Best practices

當您計畫壓縮檔案時,請考量下列資訊:Consider the following information when you plan to shrink a file:

  • 壓縮作業在截斷資料表或卸除資料表作業等建立大量未用空間的作業之後最有效。A shrink operation is most effective after an operation that creates a large amount of unused space, such as a truncate table or a drop table operation.

  • 大部分資料庫都需要一些可用空間來執行每天的例行作業。Most databases require some available free space for regular day-to-day operations. 如果您反覆壓縮資料庫,且其大小再次增加,就表示例行作業需要被壓縮的空間。If you shrink a database repeatedly and its size grows again, then it's likely that regular operations require the shrunk space. 在這些情況之下,反覆壓縮資料庫是一項會造成浪費的作業。In these cases, repeatedly shrinking the database is a wasted operation.

  • 壓縮作業不會保留資料庫中索引的片段狀態,它通常會使片段增加到某個程度。A shrink operation doesn't preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. 此片段就是不要反覆壓縮資料庫的另一個原因。This fragmentation is another reason not to repeatedly shrink the database.

  • 循序而不是同時壓縮相同資料庫中的多個檔案。Shrink multiple files in the same database sequentially instead of concurrently. 系統資料表上的競爭可能會造成封鎖,因而導致延遲。Contention on system tables can cause blocking and lead to delays.

疑難排解Troubleshooting

此章節描述如何診斷和更正在執行 DBCC SHRINKFILE 命令時可能發生的問題。This section describes how to diagnose and correct issues that can occur when running the DBCC SHRINKFILE command.

檔案未壓縮The file doesn't shrink

如果在無錯誤壓縮作業之後檔案大小不會變更,請嘗試下列命令來確認檔案具有足夠的可用空間:If the file size doesn't change after an error-less shrink operation, try the following to verify that the file has adequate free space:

  • 執行下列查詢。Run the following query.
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
  • 執行 DBCC SQLPERF 命令會傳回交易記錄中所使用的空間。Run the DBCC SQLPERF command to return the space used in the transaction log.

如果可用的空間不足,壓縮作業無法更進一步地縮減檔案大小。The shrink operation can't reduce the file size any further if there's insufficient free space available.

一般而言,呈現未壓縮狀態的都是記錄檔。Typically it's the log file that appears not to shrink. 這種未壓縮狀態通常是因為記錄檔未遭截斷而造成。This non-shrinking is usually the result of a log file that hasn't been truncated. 若要截斷記錄檔,您可以將資料庫復原模式設定為 SIMPLE,或先備份記錄檔,然後再次執行 DBCC SHRINKFILE 作業。To truncate the log, you can set the database recovery model to SIMPLE, or back up the log and then run the DBCC SHRINKFILE operation again.

壓縮作業遭到封鎖The shrink operation is blocked

以資料列版本設定為基礎的隔離等級下執行的交易可以封鎖壓縮作業。A transaction running under a row versioning-based isolation level can block shrink operations. 例如,當 DBCC SHRINK DATABASE 作業執行時,如果正在以資料列版本設定為基礎的隔離等級下進行大量刪除作業,則壓縮作業將會等到刪除作業完成之後,才會開始壓縮檔案。For example, if a large delete operation running under a row versioning-based isolation level is in progress when a DBCC SHRINK DATABASE operation executes, the shrink operation waits for the delete to complete before continuing. 當這種封鎖情況發生時,DBCC SHRINKFILE 和 DBCC SHRINKDATABASE 作業會列印資訊訊息 (SHRINKDATABASE 是 5202,SHRINKFILE 是 5203) 到 SQL Server 錯誤記錄檔中。When this blocking happens, DBCC SHRINKFILE and DBCC SHRINKDATABASE operations print out an informational message (5202 for SHRINKDATABASE and 5203 for SHRINKFILE) to the SQL Server error log. 此訊息在第一個小時內每隔五分鐘記錄一次,然後每隔一小時記錄一次。This message is logged every five minutes in the first hour and then every hour. 例如,如果錯誤記錄檔包含下列錯誤訊息,就會發生下列錯誤:For example, if the error log contains the following error message then the following error will occur:

DBCC SHRINKFILE for file ID 1 is waiting for the snapshot   
transaction with timestamp 15 and other snapshot transactions linked to   
timestamp 15 or with timestamps older than 109 to finish.  

此訊息表示時間戳記在 109 (壓縮作業所完成的最後一項交易) 之前的快照集交易將封鎖壓縮作業。This message means snapshot transactions with timestamps older than 109 (the last transaction that the shrink operation completed) are blocking the shrink operation. 這也表示 sys.dm_tran_active_snapshot_database_transactions 動態管理檢視中的 transaction_sequence_numfirst_snapshot_sequence_num 資料行包含值 15。It also indicates the transaction_sequence_num, or first_snapshot_sequence_num columns in the sys.dm_tran_active_snapshot_database_transactions dynamic management view contains a value of 15. 如果 transaction_sequence_numfirst_snapshot_sequence_num 檢視資料行所包含數字小於壓縮作業最後完成的交易 (109),壓縮作業將會等到這些交易完成。If either the transaction_sequence_num or first_snapshot_sequence_num view column contains a number less than a shrink operation's last completed transaction (109), the shrink operation waits for those transactions to finish.

若要解決這個問題,可以執行下列其中一項工作:To resolve the issue, you can do one of the following tasks:

  • 結束正在封鎖壓縮作業的交易。End the transaction that is blocking the shrink operation.
  • 結束壓縮作業。End the shrink operation. 如果壓縮作業結束,則所有已完成的工作都會保留下來。Any completed work is kept if the shrink operation ends.
  • 不執行任何動作,並允許壓縮作業等到封鎖交易完成。Do nothing and allow the shrink operation to wait until the blocking transaction completes.

權限Permissions

需要 系統管理員 固定伺服器角色或 db_owner 固定資料庫角色中的成員資格。Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

範例Examples

將資料檔案壓縮為指定的目標大小Shrinking a data file to a specified target size

下列範例會將 UserDB 使用者資料庫中名為 DataFile1 之資料檔案大小壓縮成 7 MB。The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.

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

將記錄檔壓縮為指定的目標大小Shrinking a log file to a specified target size

下列範例會將 AdventureWorks 資料庫中的記錄檔壓縮成 1 MB。The following example shrinks the log file in the AdventureWorks database to 1 MB. 若要讓 DBCC SHRINKFILE 命令可以壓縮檔案,必須先將資料庫復原模式設定為 SIMPLE 以截斷檔案。To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

USE AdventureWorks2012;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);  
GO  
-- Reset the database recovery model.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY FULL;  
GO  

C.C. 截斷資料檔案Truncating a data file

下列範例會截斷 AdventureWorks 資料庫中的主要資料檔案,The following example truncates the primary data file in the AdventureWorks database. 並查詢 sys.database_files 目錄檢視以取得資料檔案的 file_idThe sys.database_files catalog view is queried to obtain the file_id of the data file.

USE AdventureWorks2012;  
GO  
SELECT file_id, name  
FROM sys.database_files;  
GO  
DBCC SHRINKFILE (1, TRUNCATEONLY);  

D.D. 清除檔案Emptying a file

下列範例示範如何清空檔案,使其能夠從資料庫中移除。The following example demonstrates emptying a file so it can be removed from the database. 基於此範例的目的,會先建立資料檔案並包含資料。For this example's purposes, a data file is first created and contains data.

USE AdventureWorks2012;  
GO  
-- Create a data file and assume it contains data.  
ALTER DATABASE AdventureWorks2012   
ADD FILE (  
    NAME = Test1data,  
    FILENAME = 'C:\t1data.ndf',  
    SIZE = 5MB  
    );  
GO  
-- Empty the data file.  
DBCC SHRINKFILE (Test1data, EMPTYFILE);  
GO  
-- Remove the data file from the database.  
ALTER DATABASE AdventureWorks2012  
REMOVE FILE Test1data;  
GO  

另請參閱See Also

ALTER DATABASE (Transact-SQL)ALTER DATABASE (Transact-SQL)
DBCC (Transact-SQL)DBCC (Transact-SQL)
DBCC SHRINKDATABASE (Transact-SQL)DBCC SHRINKDATABASE (Transact-SQL)
FILE_ID (Transact-SQL)FILE_ID (Transact-SQL)
sys.database_files (Transact-SQL)sys.database_files (Transact-SQL)
壓縮檔案Shrink a File