在 Azure SQL Database 中管理資料庫的檔案空間

適用於：Azure SQL 資料庫

本文說明 Azure SQL Database 中資料庫的不同儲存空間類型。 你有時可能需要明確管理分配的檔案空間。 本文將說明相關步驟。

概觀

某些工作負載模式可能導致分配給資料檔案的空間超過已使用空間。 這種情況發生在資料成長導致使用空間增加，但後來又刪除或壓縮資料時。 分配但未使用的空間不會自動回收，因為回收作業資源密集，會減緩未來檔案的成長。

在以下情況下，你可能需要縮小資料檔案並回收未使用的空間：

• 當集區中某些資料庫配置的空間過大，導致集區接近其大小上限時，啟用彈性集區中資料庫的資料成長。
• 以降低單一資料庫或彈性池的最大容量。
• 將資料庫或彈性池改為最大大小限制較低的層級。
• 在使用超大規模服務層時，以降低儲存成本。

監視檔案空間使用量

Azure Resource Manager（ARM）API（包括 PowerShell get-metrics）會傳回 資料庫 和 彈性集區 的已使用和已配置的空間。

以下系統視圖也會回傳資料庫與彈性池的已使用及分配空間大小：

• sys.resource_stats
• sys.elastic_pool_resource_stats
• sys.dm_elastic_pool_resource_stats

了解資料庫的儲存體空間類型

瞭解下列儲存空間數量對於管理資料庫的檔案空間很重要。

下圖說明資料庫的不同儲存體空間類型之間的關聯性。

**

查詢單一資料庫來取得檔案空間資訊

使用 sys.database_files 上的下列查詢，以傳回已配置的資料庫檔案空間量與已配置但未使用的空間量。

-- Connect to a user database
SELECT file_id,
       type_desc,
       CAST (FILEPROPERTY(name, 'SpaceUsed') AS DECIMAL (19, 4)) * 8 / 1024. AS space_used_mb,
       CAST (size / 128.0 - CAST (FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS DECIMAL (19, 4)) AS space_unused_mb,
       CAST (size AS DECIMAL (19, 4)) * 8 / 1024. AS space_allocated_mb,
       CAST (max_size AS DECIMAL (19, 4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

了解彈性集區的儲存體空間類型

了解以下儲存空間量對於管理彈性池的檔案空間非常重要。

錯誤訊息「The elasticpool has reached its storage limit」表示資料庫物件佔用足夠空間，達到彈性池最大儲存容量限制。 考慮增加儲存空間上限，或如「 回收未使用分配空間」中所述釋放資料空間。

查詢彈性集區，以取得儲存體空間資訊

請使用以下查詢來確定彈性池的儲存空間量。

使用的彈性集區資料空間

請使用以下範例查詢來回傳所使用的彈性池資料空間量。 修改彈性池名稱參數，使其與池名相符。

-- Connect to master
SELECT TOP (1) avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS elastic_pool_space_used_mb,
               avg_allocated_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS elastic_pool_space_allocated_mb,
               elastic_pool_storage_limit_mb AS elastic_pool_maximum_size_mb
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

回收未使用的配置空間

壓縮資料檔案

由於資料檔案縮小會影響資料庫效能，Azure SQL Database 不會自動縮小資料檔案。 如果需要，你可以在自己選擇的時間縮減資料檔案。 不要將縮減作業設為定期執行的作業。 相反地，建議只有在大幅減少空間消耗後才使用。

要縮減檔案，請使用以下兩種 DBCC SHRINKDATABASEDBCC SHRINKFILE T-SQL 指令：

• DBCC SHRINKDATABASE 只需一個指令，就能將資料庫中的所有資料和日誌檔案縮減。 此命令會一次壓縮一個資料檔案，所以較大的資料庫可能需要較長的時間。 此命令也可壓縮記錄檔，但這通常不是必要措施，因為 Azure SQL Database 會視需要自動壓縮記錄檔。
• DBCC SHRINKFILE 命令支援更進階的案例：
  • 此命令可視需要壓縮個別檔案，而不是壓縮資料庫所有的檔案。
  • 每個 DBCC SHRINKFILE 命令都可以與其他 DBCC SHRINKFILE 命令並行執行，以縮短 shrink 的總耗時，但代價是更高的資源使用量，以及暫時阻塞使用者查詢和並行 DBCC SHRINKFILE 命令的機率也更高。
  • 如果檔案尾部沒有資料，你可以透過指定 TRUNCATEONLY 參數來更快減少分配的檔案大小。 TRUNCATEONLY 它不需要在檔案內移動資料，但也不會大幅減少分配的大小。
• 如需這些壓縮命令的詳細資訊，請參閱 DBCC SHRINKDATABASE 和 DBCC SHRINKFILE。

請在連線到目標使用者的資料庫時執行下列範例，而不是在連線到 master 資料庫時執行。

若要使用 DBCC SHRINKDATABASE 壓縮指定資料庫所有的資料和記錄檔：

DBCC SHRINKDATABASE (N'database_name');

資料庫可能包含一個或多個資料檔案，隨著資料成長自動建立。 要確定資料庫的檔案配置，包括每個檔案的使用與分配大小，請使用以下範例腳本查詢 sys.database_files 目錄檢視：

-- Review file properties, including the file_id and name values to use in shrink commands
SELECT file_id,
       name,
       CAST (FILEPROPERTY(name, 'SpaceUsed') AS BIGINT) * 8 / 1024. AS space_used_mb,
       CAST (size AS BIGINT) * 8 / 1024. AS space_allocated_mb,
       CAST (max_size AS BIGINT) * 8 / 1024. AS max_file_size_mb
FROM sys.database_files
WHERE type_desc IN ('ROWS', 'LOG');

要縮小單一檔案，請使用 DBCC SHRINKFILE 以下指令：

-- Shrink database data file named 'data_0` by removing all unused at the end of the file, if any.
DBCC SHRINKFILE ('data_0', TRUNCATEONLY);

壓縮交易記錄檔

不同於資料檔案，Azure SQL Database 會自動壓縮交易記錄檔，以避免可能導致空間不足錯誤的過度空間使用量。 大多數情況下，你不需要縮小交易日誌檔案。

在高級與商業關鍵服務層級，若交易日誌變大，可能會大幅增加本地儲存空間的使用量，接近 最大本地儲存 限制。 如果本地儲存空間的使用接近上限，你可以選擇像以下範例所示的 DBCC SHRINKFILE 指令來縮減交易日誌。 命令完成後便會立即釋出本機儲存體，而不必等待定期自動壓縮作業。

請在連線至目標使用者資料庫時執行下列範例，而不是在連線至 master 資料庫時執行。

-- Shrink the database log file (always file_id 2), by removing all unused space at the end of the file, if any.
DBCC SHRINKFILE (2, TRUNCATEONLY);

自動壓縮

資料庫可啟用自動壓縮，做為手動壓縮資料檔案的替代方法。 不過，自動壓縮比起 DBCC SHRINKDATABASE 和 DBCC SHRINKFILE 在回收檔案空間上可能較沒效率。

自動壓縮預設為停用，建議大部分資料庫保持此設定。 如果需要啟用自動縮減功能，建議在達成空間管理目標後關閉，而非永久啟用。 如需詳細資訊，請參閱 AUTO_SHRINK 的考量。

例如，如果彈性集區包含許多資料庫，而這些資料庫的已使用空間持續大幅增加與減少，導致集區接近其大小上限，自動縮減就會很有幫助。 此案例並不常見。

自動縮減資料庫選項在超大規模資料庫中沒有效果。

若要啟用自動壓縮，請連線至您的資料庫 (非 master 資料庫) 並執行下列命令。

-- Enable auto-shrink for the current database.
ALTER DATABASE CURRENT
    SET AUTO_SHRINK ON;

如需有關此命令的詳細資訊，請參閱 DATABASE SET 選項。

縮小後的索引維護

縮小操作完成後，索引可能會變得碎片化。 對於大多數現代平台的工作負載來說，索引碎片不太可能影響效能。 對於使用大型索引掃描的工作負載，碎片化可能會降低讀取 I/O 吞吐量。 若縮小操作完成後出現效能下降，請考慮進行索引維護以重建或重組索引。 索引重建需要資料庫中的空閒空間，因此可能會增加分配空間，抵消縮減的影響。

如需索引維修的詳細資訊，請參閱將索引維修最佳化以改善查詢效能並降低資源耗用量。

壓縮大型資料庫

當資料庫中分配的空間達到數百GB或更多時，縮減可能需要很長時間。 對於多TB資料庫，縮減作業可能持續數小時、數天甚至數週。 本節描述流程優化與最佳實務，使此流程更有效率且對應用工作負載影響較小。

擷取空間使用量基準

開始壓縮前，請執行下列空間使用量查詢，擷取各資料庫檔案目前已使用和配置的空間：

SELECT file_id,
       CAST (FILEPROPERTY(name, 'SpaceUsed') AS BIGINT) * 8 / 1024. AS space_used_mb,
       CAST (size AS BIGINT) * 8 / 1024. AS space_allocated_mb,
       CAST (max_size AS BIGINT) * 8 / 1024. AS max_size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';

完成壓縮之後，您可以再次執行此查詢，並將結果與初始基準進行比較。

截斷資料檔案以獲得快速但有限的增益

如果你想快速減少分配空間，可以考慮用參數DBCC SHRINKFILE執行TRUNCATEONLY。 如果檔案末尾有任何已分配但未使用的空間，此操作會快速移除該空間，而無需移動任何資料。

不過，如果你的目標是最大化分配空間的減少，就不要使用 TRUNCATEONLY 。 為了達成這個目標，你需要執行本節後面會描述的完整縮小流程。 因為這個過程會在最後截斷檔案，另外用一個 TRUNCATEONLY 縮小器並沒有什麼好處。

以下範例指令將檔案 ID 4 截斷：

DBCC SHRINKFILE (4, TRUNCATEONLY);

執行每個資料檔案後，重新執行空間使用查詢，看看配置空間有沒有減少。 你也可以在 Azure 入口網站查看資料庫的分配空間。

評估索引頁密度

作為一個可選但建議的步驟，請確定資料庫中索引的平均頁面密度。 對於相同資料量，若頁面密度較高，縮減操作會更快完成，因為操作在每個檔案中移動的頁面較少。 壓縮資料檔案前，如果部分索引的頁面密度較低，請考慮維修這些索引，增加頁面密度。 較高的頁面密度可讓壓縮作業進一步縮減已配置的儲存空間。

若要判斷資料庫中所有索引的頁面密度，請使用下列查詢。 頁面密度會在 avg_page_space_used_in_percent 資料行中報告。

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'SAMPLED') AS ips
     INNER JOIN sys.indexes AS i
         ON ips.object_id = i.object_id
        AND ips.index_id = i.index_id
ORDER BY page_count DESC;

如果有頁面數量高（如欄 page_count 中所述）且頁面密度低於60-70%的索引，請考慮在縮減資料檔案前重建或重新組織這些索引。

對於較大的資料庫，查詢頁面密度可能需要很長時間才能完成。 重建或重組大型指數也需要大量時間與資源投入。 然而，在縮水前進行指數維護可以縮短縮水時間並節省更高的空間。

如果有多個包含低頁面密度的索引，您可以在多個資料庫工作階段平行重建這些索引，加速處理程序。 不過，務必避免因此接近資料庫資源限制。 預留足夠的資源空間給可能正在執行的應用程式工作負載。 在Azure入口網站或使用 sys.dm_db_resource_stats 視圖監控資源消耗（CPU、資料 IO、日誌 IO）。 只有當這些維度的資源利用率都明顯低於 100%時，才可開始額外的索引操作。

範例索引重建指令

以下範例指令使用 ALTER INDEX 陳述式來重建索引並增加頁面密度：

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (
    FILLFACTOR = 100, MAXDOP = 8, ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)),
    RESUMABLE = ON
);

此命令會初始化線上且可繼續的索引重建。 此作業允許同時工作負載在重建過程中繼續使用資料表，若因任何原因中斷，則可繼續重建。 但比起會封鎖資料表存取的離線重建，此類重建的速度較慢。 重建時，如果沒有其他工作負載需要存取資料表，請將 ONLINE 和 RESUMABLE 選項設為 OFF，並移除 WAIT_AT_LOW_PRIORITY 子句。

若要深入了解索引維修，請參閱將索引維修最佳化以改善查詢效能並降低資源耗用量。

縮減前重新組織索引

在縮減前重新組織索引，可以在兩種情況下大幅加快縮減操作。

1. 如果資料庫符合以下所有條件：

   • 它擁有大量資料檔案（超過 10 個）。
   • 它在資料庫中有大量資料表（數百個以上），總共佔用大量空間（數百 GB 或更多）。
   • 部分資料表中會刪除大量資料。

   對於這類資料庫，重新組織刪除資料表上的索引，可以縮短縮減過程中的長期階段。

2. 如果資料庫包含：

   • 大型物件（LOB）資料型態，如 varchar（max）、nvarchar（max）、varbinary（max）、xml 或類似的資料型態，儲存在LOB_DATA配置單元中。
   • 大列 儲存在 ROW_OVERFLOW_DATA 分配單元中。
   • Columnstore 索引。

   為了在這種情況下讓縮減作業執行得更快並釋放更多空間，重新組織索引時務必包含 LOB_COMPACTION 子句。 建議對所有包含 LOB 欄或大列的索引在縮小之前先進行 LOB 壓縮。

   在縮減前重新組織或重建柱倉索引，也能提升縮減速度與效率。

以下範例展示了重新組織索引並執行 LOB 壓縮的指令：

ALTER INDEX [index_name] ON [schema_name].[table_name]
REORGANIZE WITH(LOB_COMPACTION = ON);

平行地縮減多個資料檔案

需要資料移動的縮減操作是一個長期運作的過程。 如果資料庫有多個資料檔案，您可以平行壓縮多個資料檔案，加速處理程序。 開啟多個資料庫會話，並在 DBCC SHRINKFILE 每個會話中使用不同的 file_id 值。 類似於先前重建索引，在開始每個新的平行壓縮命令前，請確定您有足夠的資源空餘空間 (CPU、資料 IO、記錄 IO)。

以下指令範例將檔案 ID 4 縮小，試圖將其分配大小減少至 52,000 MB：

DBCC SHRINKFILE (4, 52000);

為了將檔案分配的空間降至最小，請執行該語句但不指定目標大小：

DBCC SHRINKFILE (4);

如果你啟動過多平行的縮減作業，可能會觀察到資源使用率偏高，以及縮減作業之間出現鎖定爭用。 在大多數情境中，平行收縮操作的最佳數量約為四到八次。

逐步縮小

如果縮減作業意外停止（例如因計畫性或非計畫性維護），工作負載可能會在縮減作業截斷檔案之前，開始使用縮減作業所釋放的空間，導致縮減作業至今已完成的部分進度流失。 因為壓縮作業往往需要很長時間，因此發生中斷的機率較高。

為了避免這個問題，請以較小、循序漸進的步驟縮小每個檔案。 在指令中 DBCC SHRINKFILE ，設定目標比檔案目前分配的空間小，但大於 基線空間使用查詢 回傳的已使用空間。

例如，若檔案 ID 4 的空間為 200,000 MB，而你想將其縮小到 100,000 MB，你可以先將目標設為 180,000 MB：

DBCC SHRINKFILE (4, 180000);

此指令將分配大小縮小至 180,000 MB 後，您可以再次執行縮減，先設定目標為 160,000 MB，再設為 140,000 MB，並持續縮小直到檔案達到所需大小。

以增量縮減檔案可能會花較長時間，但能降低因意外中斷而對整個檔案重複縮減的風險。

一開始可使用 10 到 20 GB 範圍內的增量。 你可以根據情況調整增量。 較大的增量可能讓你更快完成檔案縮減，較小的增量則減少中斷縮減時失去進度的風險。

監控縮減作業

要監控所有同時執行的縮減會話的縮減進度，請使用以下查詢：

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       writes,
       CAST (((DATEDIFF(s, start_time, GETDATE())) / 3600) AS VARCHAR) + ' hour(s), '
               + CAST ((DATEDIFF(s, start_time, GETDATE()) % 3600) / 60 AS VARCHAR) + 'min, '
               + CAST ((DATEDIFF(s, start_time, GETDATE()) % 60) AS VARCHAR) + ' sec'
           AS running_time
FROM sys.dm_exec_requests AS r
     LEFT OUTER JOIN sys.databases AS d
         ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim', 'DbccFilesCompact', 'DbccLOBCompact', 'DBCC');

當所有資料檔案的縮減成功完成後，請重新執行空間使用查詢（或在 Azure 入口網站檢查），以查看分配儲存空間的減少情況。 如果已使用空間和分配空間之間的差距仍然很大，請重建或重新組織索引。 索引重建可能會暫時增加分配空間。 然而，重建索引後再次縮減資料檔案，往往會導致分配空間的更大幅減少。

縮小過程中的暫時性錯誤

有時，縮小指令可能會因逾時或死鎖等錯誤而失敗。 這些錯誤通常是短暫的，重複相同指令後不會再發生。 如果縮減因錯誤而失敗，仍會保留迄今為止的進度。 再執行一次相同的縮小指令，繼續縮小檔案。

ShrinkDriver PowerShell 指令碼會在發生暫時性錯誤時自動重試縮減作業。 使用這個腳本來縮小大型資料庫。

以下範例 T-SQL 腳本展示了如何在重試迴圈中對單一檔案執行縮減。 當發生逾時錯誤或死結錯誤時，迴圈會自動重試操作，最多可設定次數。 這種重試方法適用於縮減過程中可能發生的許多其他錯誤。

DECLARE @RetryCount AS INT = 3; -- adjust to configure desired number of retries
DECLARE @Delay AS CHAR (12);

-- Retry loop
WHILE @RetryCount >= 0
BEGIN
    BEGIN TRY
        DBCC SHRINKFILE (1); -- adjust file_id and other shrink parameters

        -- Exit retry loop on successful execution
        SELECT @RetryCount = -1;

    END TRY
    BEGIN CATCH
        -- Retry for the declared number of times without raising
        -- an error if deadlocked or timed out waiting for a lock
        IF ERROR_NUMBER() IN (1205, 49516) AND @RetryCount > 0
        BEGIN
            SELECT @RetryCount -= 1;

            PRINT CONCAT('Retry at ', SYSUTCDATETIME());

            -- Wait for a random period of time between 1 and 10 seconds before retrying
            SELECT @Delay = '00:00:0' + CAST (CAST (1 + RAND() * 8.999 AS DECIMAL (5, 3)) AS VARCHAR (5));

            WAITFOR DELAY @Delay;

        END
        ELSE -- Raise error and exit loop
        BEGIN
            SELECT @RetryCount = -1;

            THROW;

        END
    END CATCH
END

除了逾時和死結外，shrink 還可能因某些已知問題而發生錯誤。

請檢視以下章節中的錯誤與緩解步驟。

錯誤編號 49503

%.*ls: Page %d:%d could not be moved because it is an off-row persistent version store page. Page holdup reason: %ls. Page holdup timestamp: %I64d.

當長時間執行的作用中交易在持續版本存放區 (PVS) 中產生資料列版本時，就會發生此錯誤。 Shrink 無法移動包含排版本的頁面。

為避免發生此錯誤，請等到長時間執行的交易作業完成。 或者，可以識別並終止長期執行的交易，但如果應用程式無法妥善處理交易失敗，這個動作可能會影響到你的應用程式。

欲了解更多關於可能影響縮減的 PVS 清理延遲故障排除資訊，請參閱「 監控」及「加速資料庫復原故障排除」。

錯誤編號 5223

%.*ls: Empty page %d:%d could not be deallocated.

此錯誤可能發生在持續進行的索引維護操作中，例如 ALTER INDEX。 完成這些作業後，請重試壓縮命令。

如果這個錯誤持續存在，你可能需要重建相關的索引。 若要尋找要重建的索引，請在執行壓縮命令的相同資料庫中，執行下列查詢：

SELECT OBJECT_SCHEMA_NAME(pg.object_id) AS schema_name,
       OBJECT_NAME(pg.object_id) AS object_name,
       i.name AS index_name,
       p.partition_number
FROM sys.dm_db_page_info(DB_ID(), <file_id>, <page_id>, default) AS pg
INNER JOIN sys.indexes AS i
ON pg.object_id = i.object_id
   AND
   pg.index_id = i.index_id
INNER JOIN sys.partitions AS p
ON pg.partition_id = p.partition_id;

執行此查詢前，請將 <file_id> 和 <page_id> 這兩個預留位置取代為錯誤訊息中的實際值。 例如，若訊息為：Empty page 1:62669 could not be deallocated，則 <file_id> 是 1，且 <page_id> 是 62669。

重建查詢所識別的索引，並重試壓縮命令。

錯誤編號 5201

DBCC SHRINKDATABASE: File ID %d of database ID %d was skipped because the file does not have enough free space to reclaim.

此錯誤表示資料檔案無法再縮小。 您可以移至下一個資料檔案。

相關內容

• 使用虛擬核心購買模型的單一資料庫資源限制
• 使用 DTU 購買模型的單一資料庫資源限制 - Azure SQL 資料庫
• 使用虛擬核心購買模型的彈性集區資源限制
• 使用 DTU 購買模型的彈性集區資源限制