在 Azure SQL 受控執行個體中管理資料庫的檔案空間

適用於：Azure SQL 受控執行個體

• Azure SQL 資料庫
• Azure SQL 受控執行個體

本文說明如何在 Azure SQL 受控執行個體中監視和管理資料庫中的檔案。 它涵蓋如何監視資料庫檔案大小、縮小交易記錄檔、放大交易記錄檔，以及控制交易記錄檔的成長。

本文適用於 Azure SQL 受控執行個體。 如需在 SQL Server 中管理交易記錄檔大小的相關資訊，請參閱 管理交易記錄檔的大小。

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

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

資料庫數量	定義	註解
已使用的資料空間	用來儲存資料庫資料的空間量。	一般而言，使用的空間會在插入 (刪除) 時增加 (減少)。 在某些情況下，使用的空間不會在插入或刪除時變更，具體取決於作業中涉及的資料量和模式，以及任何片段。 例如，從每個資料頁面刪除一個資料列，不見得會減少使用的空間。
已配置的資料空間	可用以儲存資料庫資料的格式化檔案空間量。	配置的空間量會自動成長，但絕不會在刪除後減少。 此行為可確保未來的插入速度更快，因為不需要重新格式化空間。
已配置但未使用的資料空間	已配置的資料空間量與已使用的資料空間之間的差異。	此數量代表可藉由壓縮資料檔案而回收的可用空間量上限。
資料大小上限	可用來儲存資料庫資料的空間量上限。	配置的資料空間量不可成長超過資料大小上限。

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

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

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

-- 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;

監視記錄空間的使用

使用 sys.dm_db_log_space_usage 來監視記錄空間的使用。 這個 DMV 會傳回目前使用之記錄空間量的相關資訊，並指出交易記錄需要截斷的時機。

如需目前記錄檔大小、大小上限，以及檔案自動成長選項的相關資訊，請在sizesys.database_files中使用該記錄檔的 、 max_size和growth資料行。

Azure Resource Manager 計量型 API 中顯示的儲存空間計量僅測量已使用的資料頁面大小。 如需範例，請參閱 PowerShell Get-AZMetric。

壓縮記錄檔大小

若要藉由移除未使用之空間來減少實體記錄檔的實體大小，必須壓縮記錄檔。 當交易記錄檔包含未使用的空間時，壓縮才會產生差異。 如果記錄檔已滿 (可能是因為開啟的交易)，請調查阻止交易記錄截斷的原因。

警告

壓縮作業不應被視為定期維護作業。 由於定期、週期性商務作業而成長的資料和記錄檔不需要壓縮作業。 壓縮命令會在資料庫執行時影響其效能，如果可能，應該在低使用量期間執行。 如果一般應用程式工作負載導致檔案再次成長到相同的配置大小，則不建議縮小資料檔案。

請注意縮小資料庫檔案對效能的潛在負面影響。 如需詳細資訊，請參閱 縮減後的索引維護。 在極少數情況下， 自動化資料庫備份 可能會影響壓縮作業。 如有必要，請重試壓縮作業。

在壓縮交易日誌之前，請記住 可能延遲日誌截斷的因素。 如果在記錄壓縮之後再次需要儲存空間，交易記錄會再次成長，藉此執行此動作，會在記錄成長作業期間引入效能額外負荷。 如需詳細資訊，請參閱 建議 一節。

只有當資料庫已上線，而且至少有一個虛擬記錄檔 (VLF) 可用時，您才能壓縮記錄檔。 在某些情況下，壓縮記錄可能要等到下一個記錄截斷之後才能進行。

如長時間執行的交易等因素，會使 VLF 保持作用中一段很長的時間，可能會限制或甚至完全不能縮減記錄檔的大小。 如需資訊，請參閱可能會延遲記錄截斷的因素。

縮小記錄檔會移除一或多個不包含任何邏輯記錄的 VLF（即「非使用中 VLF」）。 正在縮減交易日誌檔案時，會從日誌的結尾移除未使用的 VLF，以將日誌縮減至接近目標大小。

如需壓縮作業的詳細資訊，請檢閱下列文件：

壓縮記錄檔 (但不壓縮資料庫檔案)

• DBCC 壓縮檔案 （Transact-SQL）

• 壓縮檔案

監視記錄檔壓縮事件

• Log File 自動縮小事件類別。

監視記錄空間

• sys.dm_db_log_space_usage （Transact-SQL）

• sys.database_files (Transact-SQL) (請參閱記錄檔的 size、max_size 和 growth 資料行。)

縮小後的索引維護

完成資料檔案的壓縮作業後，索引可能會變得分散。 片段化會降低索引對特定工作負載的效能最佳化有效性，例如使用大型掃描的查詢。 如果壓縮作業完成後發生效能降低的情形，請考慮執行索引維修來重建索引。 請記得，索引重建需要資料庫具有可用空間，並可能導致已配置的空間增加，抵銷壓縮的效果。

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

評估索引頁密度

如果截斷資料檔案不會導致配置空間充分減少，您可能會決定壓縮資料庫資料檔案，以從這些檔案中回收未使用的空間。 但請先判斷資料庫索引的頁面平均密度，這個步驟為選擇性但建議您採用。 對於相同數量的資料，如果頁面密度很高，則收縮會更快完成，因為它移動的頁面較少。 壓縮資料檔案前，如果部分索引的頁面密度較低，請考慮維修這些索引，增加頁面密度。 此步驟可讓收縮更深入地減少分配的儲存空間。

若要判斷資料庫中所有索引的頁面密度，請使用下列查詢。 頁面密度會在 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;

壓縮資料檔案前，如果索引包含高頁面計數，但頁面密度低於 60-70%，請考慮重建或重新組織這些索引。

注意

如果是較大的資料庫，判斷頁面密度的查詢可能需要較長時間 (數小時) 才能完成。 此外，重建或重新組織大型索引也需要大量的時間和資源使用量。 一方面，花費額外的時間增加頁面密度，另一方面減少收縮持續時間並節省更高的空間，兩者之間需要權衡。

如果有多個包含低頁面密度的索引，您可以在多個資料庫工作階段平行重建這些索引，加速處理程序。 不過，請確定這樣做不會接近資料庫資源限制，並為應用程式工作負載留出足夠的資源空間。 在 Azure 入口網站中監視資源耗用量 （CPU、資料 IO、記錄 IO），或使用 sys.dm_db_resource_stats 檢視。 只有在每個維度上的資源使用率仍大幅低於 100%時，才開始進一步的平行重建。 如果 CPU、資料 IO 或記錄 IO 使用率為 100%，您可以相應增加資料庫以擁有更多 CPU 核心並增加 IO 輸送量，從而允許更多平行重建以更快地完成程序。

索引重建命令範例

以下是使用 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 子句。

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

壓縮多個資料檔案

如先前所述，隨著資料的移動而進行的縮減是一個耗時的過程。 如果資料庫有多個資料檔案，您可以平行壓縮多個資料檔案，加速處理程序。 您可以開啟多個資料庫工作階段，並在每個工作階段上使用 DBCC SHRINKFILE 不同的 file_id 值來執行此作業。 類似於先前重建索引，在開始每個新的平行壓縮命令前，請確定您有足夠的資源空餘空間 (CPU、資料 IO、記錄 IO)。

下列範例命令會壓縮 file_id 4 的資料檔案，並移動檔案中的頁面，嘗試減少配置大小至 52,000 MB：

DBCC SHRINKFILE (4, 52000);

若要盡量減少檔案的配置空間，在執行陳述式時，請不要指定目標大小：

DBCC SHRINKFILE (4);

如果工作負載與壓縮同時執行，工作負載可能會在壓縮完成並截斷檔案前就開始使用壓縮所釋放出來的儲存空間。 在此情況下，收縮無法將配置的空間減少至指定的目標。

您可以透過以較小的步驟縮小每個檔案來緩解此問題。 這表示在 DBCC SHRINKFILE 命令中，設定略小於檔案目前配置空間的目標。 例如，如果 file_id 4 的檔案配置空間為 200,000 MB，而您想要將其壓縮為 100,000 MB，可先將目標設為 170,000 MB：

DBCC SHRINKFILE (4, 170000);

此命令完成後，它會截斷檔案並將其分配的大小減少到 170,000 MB。 然後，您可以重複此指令，先將目標設定為 140,000 MB，然後設定為 110,000 MB，依此類推，直到檔案縮小到所需的大小為止。 如果命令完成，但檔案未被截斷，請使用較小的步驟，例如 15,000 MB 而不是 30,000 MB。

若要監控所有同時執行的縮小工作階段的進度，您可以使用下列查詢：

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       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 JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

注意

收縮進度可以是非線性的，即使收縮仍在進行中，直欄中的 percent_complete 值也可能長時間保持不變。

完成所有資料檔案的壓縮之後，請使用 空間使用量查詢 來判斷所配置儲存體大小的減少。 如果已使用空間與已配置空間之間仍有很大差異，您可以 重建索引。 重建可以暫時進一步增加配置的空間，但是在重建索引之後再次壓縮數據檔應該會導致配置空間的更深減少。

放大記錄檔

在 Azure SQL 受控執行個體中，如果磁碟空間允許，您可以藉由放大現有的記錄檔來將空間新增至記錄檔。 不支援將記錄檔新增至資料庫。 除非記錄空間用完，而且磁碟區上的磁碟空間也會用盡，否則一個事務歷史記錄檔就已足夠。

若要放大日誌檔，請使用 MODIFY FILE 陳述式的 ALTER DATABASE 子句，並指定 SIZE and MAXSIZE 語法。 如需詳細資訊，請參閱 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項。

如需詳細資訊，請參閱建議。

控制交易記錄檔的成長

若要管理交易記錄檔的成長，請使用 ALTER DATABASE （Transact-SQL） File 和 Filegroup 選項 陳述式。 請注意下列選項：

• 使用該 SIZE 選項以 KB、MB、GB 和 TB 為單位變更目前檔案大小。
• 使用選項 FILEGROWTH 來變更成長增量。 0 的值表示將自動成長設為關閉，且不允許任何額外的空間。
• 使用該 MAXSIZE 選項來控制日誌檔的大小上限，以 KB、MB、GB 及 TB 為單位，或將成長設為 UNLIMITED。

建議

當您使用交易記錄檔時，請考慮下列建議：

• 設定交易日誌的自動成長 （自動成長） 增量 （如選項 FILEGROWTH 所設定） ，使其足夠大，以符合工作量交易的需求。 讓日誌檔上的檔案成長增量足夠大，以避免頻繁擴充。 您可以監視下列期間佔用的記錄數量，以適當調整交易記錄的大小：

  • 執行完整備份所需的時間，因為記錄備份在完成之前無法發生。
  • 最大索引維護作業所需的時間。
  • 執行資料庫中最大批次所需的時間。

• 使用 中的選項 而不是 sizepercentage，為FILEGROWTH資料和日誌檔設定自動增長，以便更好地控制增長比率，因為百分比是不斷增長的量。

  • 在 Azure SQL 受控執行個體中，檔案立即初始化可讓交易記錄成長事件受益多達 64 MB。 新資料庫的默認自動成長大小增量為 64 MB。 大於 64 MB 的交易記錄檔自動成長事件無法受益於檔案立即初始化。
  • 最佳做法是，請勿針對事務歷史記錄設定 FILEGROWTH 高於 1,024 MB 的選項值。

• 避免設定較小的自動成長增量，因為它會產生太多小型 VLF 並降低效能。 若要判斷指定執行個體中所有資料庫的目前交易記錄大小的最佳 VLF 分佈，以及達到所需大小的必要增長增量，請參閱 SQL Tiger 團隊提供的用於分析和修正 VLF 的指令碼。

• 避免設定較大的自動成長增量，因為它可能會導致兩個問題：

  • 配置新空間時，資料庫可能會暫停，這可能會導致查詢逾時。
  • 它可能會產生數量太少且大小過大的VLF，也會影響效能。 若要判斷指定執行個體中所有資料庫的目前交易記錄大小的最佳 VLF 分佈，以及達到所需大小的必要增長增量，請參閱 SQL Tiger 團隊提供的用於分析和修正 VLF 的指令碼。

• 即使已啟用自動增長，如果交易記錄無法成長到足夠快來滿足查詢的需求，您仍可能會收到一則訊息，指出交易記錄已滿。 如需變更成長增量的詳細資訊，請參閱 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項。

• 您可以將日誌檔設定為自動縮小。 不過， 不建議使用此做法，而且預設情況下， auto_shrink 資料庫屬性會設定為 FALSE。 如果您將 auto_shrink 設定為 TRUE，則只有在超過 25% 的空間未使用時，自動壓縮才會減少檔案的大小。

  • 檔案會縮小至只有 25% 的檔案未使用空間的大小，或縮小至檔案的原始大小，以較大者為準。
  • 如需變更 auto_shrink 屬性設定的資訊，請參閱檢視或變更資料庫的屬性和 ALTER DATABASE SET 選項 (Transact-SQL)。

相關內容

• Azure SQL 受控執行個體中的自動化備份‭
• ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項
• Azure SQL 受控執行個體資源限制概觀
• 針對 Azure SQL 受控執行個體的交易記錄錯誤進行疑難排解