DBCC SHRINKDATABASE (Transact-SQL)DBCC SHRINKDATABASE (Transact-SQL)

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

壓縮指定之資料庫中的資料和記錄檔大小。Shrinks the size of the data and log files in the specified database.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { NOTRUNCATE | TRUNCATEONLY } ]   
)  
[ WITH NO_INFOMSGS ]  
-- Azure Synapse Analytics (formerly SQL DW)

DBCC SHRINKDATABASE   
( database_name   
     [ , target_percent ]   
)  
[ WITH NO_INFOMSGS ]

注意

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

引數Arguments

database_name | database_id | 0database_name | database_id | 0
這是要壓縮的資料庫名稱或識別碼。Is the database name or ID to be shrunk. 0 指定使用目前的資料庫。0 specifies that the current database is used.

target_percenttarget_percent
這是壓縮資料庫之後,資料庫檔案所要保留的可用空間百分比。Is the percentage of free space that you want left in the database file after the database has been shrunk.

NOTRUNCATENOTRUNCATE
將所指派頁面從檔案結尾移動到檔案前面未指派的頁面。Moves assigned pages from the file's end to unassigned pages in the front of the file. 此動作會壓縮檔案內的資料。This action compacts the data within the file. target_percent 是選擇性的。target_percent is optional. Azure Synapse Analytics (SQL 資料倉儲)Azure Synapse Analytics (SQL Data Warehouse) 不支援此選項。doesn't support this option.

檔案結尾的可用空間並不會還給作業系統,檔案的實際大小也不會改變。The free space at the end of the file isn't returned to the operating system, and the physical size of the file doesn't change. 因此,當您指定 NOTRUNCATE 時資料庫似乎不會壓縮。As such, the database appears not to shrink when you specify NOTRUNCATE.

NOTRUNCATE 只適用於資料檔案。NOTRUNCATE is applicable only to data files. NOTRUNCATE 不會影響記錄檔。NOTRUNCATE doesn't affect the log file.

TRUNCATEONLYTRUNCATEONLY
將檔案結尾的所有可用空間釋放給作業系統。Releases all free space at the end of the file to the operating system. 不會在檔案內移動任何頁面。Doesn't move any pages inside the file. 資料檔案只會壓縮到最後一個指派的範圍。The data file shrinks only to the last assigned extent. 如果在使用 TRUNCATEONLY 時指定 target_percent,則會予以忽略。Ignores target_percent if specified with TRUNCATEONLY. Azure Synapse Analytics (SQL 資料倉儲)Azure Synapse Analytics (SQL Data Warehouse) 不支援此選項。doesn't support this option.

TRUNCATEONLY 會影響記錄檔。TRUNCATEONLY affects the log file. 若要只能截斷資料檔案,請使用 DBCC SHRINKFILE。To truncate only the data file, use DBCC SHRINKFILE.

WITH NO_INFOMSGSWITH NO_INFOMSGS
抑制所有嚴重性層級在 0 到 10 的參考用訊息。Suppresses all informational messages that have severity levels from 0 through 10.

結果集Result Sets

下表描述結果集中的資料行。The following table describes the columns in the result set.

資料行名稱Column name 描述Description
DbIdDbId Database EngineDatabase Engine 試圖壓縮之檔案的資料庫識別碼。Database identification number of the file the Database EngineDatabase Engine tried to shrink.
FileIdFileId Database EngineDatabase Engine 試圖壓縮之檔案的識別碼。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 value 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.

注意

Database EngineDatabase Engine 不會顯示未壓縮之檔案的資料列。The Database EngineDatabase Engine does not display rows for those files not shrunk.

備註Remarks

注意

不建議執行此命令,因為這是 I/O 密集作業,而且可以讓您的資料倉儲離線。Running this command is not recommended as this is an i/o intensive operation and can take your data warehouse offline. 此外,執行此命令之後將會對您的資料倉儲快照集成本有所影響。In addition, there will be costing implications to your data warehouse snapshots after running this command.

若要壓縮特定資料庫的所有資料和記錄檔,請執行 DBCC SHRINKDATABASE 命令。To shrink all data and log files for a specific database, execute the DBCC SHRINKDATABASE command. 若要一次壓縮特定資料庫的一個資料或記錄檔,請執行 DBCC SHRINKFILE 命令。To shrink one data or log file at a time for a specific database, execute the DBCC SHRINKFILE command.

若要檢視資料庫中目前的可用 (未配置) 空間量,請執行 sp_spaceusedTo view the current amount of free (unallocated) space in the database, run sp_spaceused.

在這個處理序中,隨時可以停止 DBCC SHRINKDATABASE 作業,任何已完成的工作都會保留下來。DBCC SHRINKDATABASE operations can be stopped at any point in the process, and any completed work is kept.

資料庫的大小不得小於設定的資料庫大小下限。The database can't be smaller than the configured minimum size of the database. 最初建立資料庫時,您會指定大小下限。You specify the minimum size when the database is originally created. 或者,大小下限也可以是最後一次使用檔案大小變更作業明確設定的大小。Or, the minimum size can be the last size explicitly set by using a file size changing operation. 像是 DBCC SHRINKFILE 或 ALTER DATABASE 作業都是檔案大小變更作業的範例。Operations like DBCC SHRINKFILE or ALTER DATABASE are examples of file-size changing operations.

假設資料庫最初建立大小為 10 MB 的大小。Let's say a database is originally created with a size of 10 MB in size. 然後,它成長到 100 MB。Then, it grows to 100 MB. 即使已刪除資料庫中的所有資料,資料庫可縮減為最小程度便是 10 MB。The smallest the database can be reduced to is 10 MB, even if all the data in the database has been deleted.

當您執行 DBCC SHRINKDATABASE 時,請指定 NOTRUNCATE 選項或 TRUNCATEONLY 選項。Specify either the NOTRUNCATE option or the TRUNCATEONLY option when you run DBCC SHRINKDATABASE. 如果不這麼做,結果會等同於您執行 DBCC SHRINKDATABASE 作業並使用 NOTRUNCATE,然後再執行 DBCC SHRINKDATABASE 作業並使用 TRUNCATEONLY。If you don't, the result is the same as if you run a DBCC SHRINKDATABASE operation with NOTRUNCATE followed by running a DBCC SHRINKDATABASE operation with TRUNCATEONLY.

壓縮的資料庫不一定要處於單一使用者模式。The shrunk database doesn't have to be in single user mode. 其他使用者可以在壓縮時使用資料庫,包括系統資料庫。Other users can be working in the database when it's shrunk, including system databases.

資料庫在備份時不能進行壓縮。You can't shrink a database while the database is being backed up. 反過來說,當資料庫上正在進行壓縮作業時,也不能對其進行備份。Conversely, you can't back up a database while a shrink operation on the database is in process.

DBCC SHRINKDATABASE 的運作方式How DBCC SHRINKDATABASE Works

DBCC SHRINKDATABASE 會以個別檔案為基礎來壓縮資料檔案,但會依照所有記錄檔都是在單一連續記錄集區的方式來壓縮記錄檔。DBCC SHRINKDATABASE shrinks data files on a per-file basis, but shrinks log files as if all the log files existed in one contiguous log pool. 檔案必定從結尾處進行壓縮。Files are always shrunk from the end.

假設您有幾個記錄檔、一個資料檔,以及一個名為 mydb 的資料庫。Assume you have a couple of log files, a data file, and a database named mydb. 每個資料檔案和記錄檔均為 10 MB,資料檔案則包含 6 MB 的資料。The data and log files are 10 MB each and the data file contains 6 MB of data. Database EngineDatabase Engine 會計算每個檔案的目標大小。The Database EngineDatabase Engine calculates a target size for each file. 這個值是檔案要壓縮到的大小。This value is the size to which the file is to be shrunk. 當設定 target_percent 來指定 DBCC SHRINKDATABASE 時,Database EngineDatabase Engine 會將目標大小計算為在壓縮之後,檔案中可用空間的 target_percent 量。When DBCC SHRINKDATABASE is specified with target_percent, the Database EngineDatabase Engine calculates target size to be the target_percent amount of space free in the file after shrinking.

例如,如果您指定壓縮 mydbtarget_percent 為 25,則 Database EngineDatabase Engine 會將這個資料檔案的目標大小計算為 8 MB (6 MB 資料加 2 MB 可用空間)。For example, if you specify a target_percent of 25 for shrinking mydb, the Database EngineDatabase Engine calculates the target size for the data file to be 8 MB (6 MB of data plus 2 MB of free space). 因此,Database EngineDatabase Engine 會將資料檔案最後 2 MB 的任何資料移到資料檔案前 8 MB 中的任何可用空間,然後再壓縮檔案。As such, the Database EngineDatabase Engine moves any data from the data file's last 2 MB to any free space in the data file's first 8 MB and then shrinks the file.

假設 mydb 的資料檔案包含 7 MB 的資料。Assume the data file of mydb contains 7 MB of data. target_percent 指定為 30,可以將這個資料檔案壓縮到可用百分比 30。Specifying a target_percent of 30 allows for this data file to be shrunk to the free percentage of 30. 不過,將 target_percent 指定為 40 並不會壓縮資料檔案,因為 Database EngineDatabase Engine 不會將檔案壓縮成小於資料目前所佔用的大小。However, specifying a target_percent of 40 doesn't shrink the data file because the Database EngineDatabase Engine won't shrink a file to a size smaller than the data currently occupies.

您也可以用另一種方法思考此問題:40% 需要的可用空間 + 70% 完整資料檔案 (10 MB 中的 7 MB) 會超出 100%。You can also think of this issue another way: 40 percent wanted free space + 70 percent full data file (7 MB out of 10 MB) is more than 100 percent. 大於 30 的任何 target_size 都不會壓縮資料檔案。Any target_size greater than 30 won't shrink the data file. 它不會壓縮,因為您想要的可用百分比加上目前資料檔案所佔百分比已超過 100%。It won't shrink because the percentage free you want plus the current percentage that the data file occupies is over 100 percent.

針對記錄檔,Database EngineDatabase Engine 會使用 target_percent 來計算整份記錄的目標大小。For log files, the Database EngineDatabase Engine uses target_percent to calculate the target size for the whole log. 這就是為什麼 target_percent 是壓縮作業之後的記錄檔可用空間量。That's why target_percent is the amount of free space in the log after the shrink operation. 之後,便會將整份記錄的目標大小轉換成每個記錄檔的目標大小。Target size for the whole log is then translated to a target size for each log file.

DBCC SHRINKDATABASE 會試圖將每個實體記錄檔立即壓縮成目標大小。DBCC SHRINKDATABASE tries to shrink each physical log file to its target size immediately. 例如,假設邏輯記錄檔在超出記錄檔目標大小之後,沒有任何部分能留在虛擬記錄檔中。Let's say no part of the logical log stays in the virtual logs beyond the target size of the log file. 那麼會順利截斷檔案,且 DBCC SHRINKDATABASE 會完成而不會有任何訊息。Then the file is successfully truncated and DBCC SHRINKDATABASE finishes without any messages. 不過,如果邏輯記錄有任何部分會在超出目標大小時留在虛擬記錄中,Database EngineDatabase Engine 會盡可能釋出空間,然後發出一則參考用訊息。However, if part of the logical log stays 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 SHRINKDATABASE 來釋出其餘空間。After the actions are run, DBCC SHRINKDATABASE can be used to free the remaining space.

記錄檔只能壓縮到虛擬記錄檔界限。A log file can only be shrunk to a virtual log file boundary. 這就是為什麼可能無法將記錄檔壓縮成小於虛擬記錄檔大小的大小。That's why shrinking a log file to a size smaller than the size of a virtual log file might not be possible. 即使並未正在使用它也可能無法這麼做。It might not be possible even if it isn't being used. 建立或擴充記錄檔時,Database EngineDatabase Engine 會動態選擇虛擬記錄檔的大小。The size of the virtual log file is chosen dynamically by the Database EngineDatabase Engine when log files are created or extended.

最佳做法Best Practices

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

  • 壓縮作業在進行會產生未用空間的作業 (例如截斷資料表或卸除資料表作業) 之後最有效。A shrink operation is most effective after an operation that creates unused space, such as a truncate table or a drop table operation.
  • 大部分資料庫都需要一些可用空間來執行每天的例行作業。Most databases require some free space to be available for regular day-to-day operations. 您可以反覆壓縮資料庫,且注意到資料庫大小再次成長。You might shrink a database repeatedly and notice that the database size grows again. 這種成長表示例行作業需要壓縮空間。This growth indicates that the shrunken space is required for regular operations. 在這些情況之下,反覆壓縮資料庫是一項會造成浪費的作業。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 result is another reason not to repeatedly shrink the database.
  • 除非您有特定的需求,否則請不要將 AUTO_SHRINK 資料庫選項設定為 ON。Unless you have a specific requirement, don't set the AUTO_SHRINK database option to ON.

疑難排解Troubleshooting

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

DBCC SHRINKDATABASE for database ID 9 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 error means snapshot transactions that have timestamps older than 109 will block the shrink operation. 該交易是壓縮作業完成的最後一個交易。That transaction is the last transaction that the shrink operation completed. 其也表示 sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) 動態管理檢視中的 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 (Transact-SQL) dynamic management view contain a value of 15. 檢視中的 transaction_sequence_numfirst_snapshot_sequence_num 資料行所包含數字可能小於壓縮作業所完成的最後一項交易 (109)。The transaction_sequence_num or first_snapshot_sequence_num column in the view might contain a number that is less than the last transaction completed by a shrink operation (109). 如果是這樣,壓縮作業將會等到這些交易完成。If so, the shrink operation will wait for those transactions to finish.

若要解決這個問題,可以執行下列其中一項工作:To resolve the problem, 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.
  • 不執行任何動作,並允許壓縮作業等到封鎖交易完成。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

A.A. 壓縮資料庫並指定可用空間百分比Shrinking a database and specifying a percentage of free space

下列範例會縮小 UserDB 使用者資料庫中的資料和記錄檔大小,使資料庫中能有 10% 的可用空間。The following example reduces the size of the data and log files in the UserDB user database to allow for 10 percent free space in the database.

DBCC SHRINKDATABASE (UserDB, 10);  
GO  

B.B. 截斷資料庫Truncating a database

下列範例會將 AdventureWorks 範例資料庫中資料檔案和記錄檔壓縮為最後指派的範圍。The following example shrinks the data and log files in the AdventureWorks sample database to the last assigned extent.

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY);  

C.C. 縮小 Azure Synapse Analytics 資料庫Shrinking an Azure Synapse Analytics database

DBCC SHRINKDATABASE (database_A);
DBCC SHRINKDATABASE (database_B, 10); 

另請參閱See also

ALTER DATABASE (Transact-SQL)ALTER DATABASE (Transact-SQL)
DBCC (Transact-SQL)DBCC (Transact-SQL)
DBCC SHRINKFILE (Transact-SQL)DBCC SHRINKFILE (Transact-SQL)
壓縮資料庫Shrink a Database