ApsaraDB RDS：RDS SQL Server磁碟空間滿問題

分析原因

資料空間的總大小（即所有資料檔案大小總和）是由已指派（Allocated）空間和未分配（Unallocated）空間兩部分組成：

• 已指派空間包括已使用（Used）空間和未使用（Unused）空間，未使用空間只能分配給同一表或索引新增的記錄使用，其他資料庫物件無法直接使用。

• 未配置的空間是由完全未分配的區（Extent）構成的，每個區是連續的64 KB空間。未配置的空間不和任何資料庫物件關聯，這部分空間可以通過收縮檔案的方式釋放。

在資料量持續增長的情況下，未配置的空間通常很小，所以直接收縮檔案效果很差，建議首先對已指派空間進行最佳化和回收，然後再考慮收縮檔案。

回收資料空間

通常資料空間的回收有如下幾種方式：

• 歸檔資料

  刪除資料庫中不常用的資料（例如早期的歷史資料），或者根據需要遷移到其他資料庫執行個體中，或者以其他形式歸檔儲存，通過直接減少資料量來降低已使用資料空間大小。

  這種方式是控制資料空間增長的有效手段，但是對資料庫物件結構及相關應用邏輯的設計有一定要求，需要應用設計和開發人員的參與配合。

• 壓縮資料

  SQL Server 2016及以上版本執行個體，或2016以下的企業版執行個體，內建資料壓縮功能，您可以在單個表、索引或分區上開啟壓縮功能，包括行壓縮和頁壓縮。更多資訊，請參見Data Compression。

  資料壓縮比由表結構、列的資料類型和數值分布情況等決定，跨度較大，例如從10%到90%。SQL Server中提供了一個專用的預存程序sp_estimate_data_compression_savings可以協助您快速評估在指定的表或索引上開啟壓縮可以節省多少資料存放區空間。

  說明

  • 修改表或索引上的壓縮選項設定是DDL操作，大表執行此類操作會造成長時間鎖表，可能影響業務，建議在業務低峰期修改。

  • RDS SQL Server企業版執行個體可以設定參數ONLINE為ON，然後執行修改操作，基本不會影響正常業務。

  • 資料壓縮會增加CPU開銷，因此需要根據實際業務情況進行評估，建議只在大表上啟用資料壓縮。

• 整理索引片段

  索引片段率較高會導致實際佔用的資料存放區空間過大，因此對索引執行磁碟重組可以降低資料空間大小。

  您可以在控制台的自治服務（原CloudDBA） > 性能優化頁面，單擊索引使用率頁簽，可以查看各表的索引片段率統計結果，並且自治服務會提供索引重建（Rebuild）或重組（Reorganize）建議。

  • 重建（Rebuild）操作

    最佳化效果較好，對於片段率高的情況執行效率更高，預設情況下執行過程中會鎖表，企業版中可以設定參數ONLINE為ON避免長時間鎖表。

  • 重組（Reorganize）操作

    對於片段率較低的情況執行效率更高，但最佳化效果不如重建。

  索引片段率統計的是邏輯上相鄰的索引頁在物理位置上不一致的比例，和索引頁中的空閑空間比例不是一個概念，只是片段率較高的索引大機率也是可回收空間比例較大的索引。

  如果需要分析某個索引的頁內平均空閑空間比例，可以使用SAMPLED或DETAILED模式查詢系統檢視表sys.dm_db_index_physical_stats，然後參考結果集中avg_page_space_used_in_percent列的值。更多資訊，請參見sys.dm_db_index_physical_stats (Transact-SQL)。

  說明

  查詢過程會讀取大量索引頁，可能影響資料庫效能，請在業務低峰期操作。

  整理索引片段只適用於更新頻率很低的歸檔資料表，如果有頻繁的插入和更新操作，索引片段率會很快升高，而且重建或重組的過程中，會產生大量交易記錄，導致日誌空間的增加。

收縮資料檔案

上文資料空間回收操作通常可以有效降低資料空間大小，但如果執行個體空間仍然有壓力，可以通過以下兩種方式解決：

• 方式一：分批次迴圈收縮資料檔案，建議每次收縮5 GB。樣本如下：

  DECLARE @dbName NVARCHAR(128) = 'YourDatabaseName'  -- 資料庫名稱
  DECLARE @fileName NVARCHAR(128)  -- 資料檔案名
  DECLARE @targetSize INT = 1024   -- 目標大小（MB）
  DECLARE @shrinkSize INT = 5120   -- 迴圈收縮時每次收縮的大小（MB），建議每次5GB
  DECLARE @currentSize INT         -- 當前大小
  DECLARE @sql NVARCHAR(500)       
  DECLARE @waitTime INT = 10      -- 每次收縮後等待時間（秒）
  
  -- 擷取資料檔案名
  SELECT @fileName = name
  FROM sys.master_files
  WHERE database_id = DB_ID(@dbName)
  AND type_desc = 'ROWS'
  
  -- 迴圈收縮
  WHILE 1 = 1
  BEGIN
      -- 擷取當前檔案大小
      SELECT @currentSize = size/128
      FROM sys.database_files
      WHERE name = @fileName
      
      -- 如果達到目標大小則退出
      IF @currentSize <= @targetSize
      BEGIN
          PRINT '收縮完成，當前大小: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
          BREAK
      END
      
      -- 計算本次收縮後的大小
      DECLARE @newSize INT = @currentSize - @shrinkSize
      IF @newSize < @targetSize
          SET @newSize = @targetSize
          
      -- 執行收縮
      SET @sql = 'DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
      PRINT '執行收縮: ' + @sql
      EXEC (@sql)
      
      -- 等待一段時間後繼續
      PRINT '等待 ' + CAST(@waitTime AS VARCHAR(10)) + ' 秒後繼續...'
      WAITFOR DELAY '00:05:00'
  END

• 方式二：執行DBCC SHRINKFILE命令直接收縮單個資料檔案，將資料檔案中未配置的空間釋放給作業系統。更多資訊，請參見Shrink a Database和DBCC SHRINKFILE (Transact-SQL)。

  DBCC SHRINKFILE(<檔案ID>, <期望收縮後的大小（單位MB）>)

  點擊展開查看樣本

  以上圖為例，一個區（Extent）的大小為64 KB，資料檔案的總空間大小為（1673344 x 64）/ 1024 = 104584 MB，已指派空間為（1313432 x 64）/ 1024 = 82089.5 MB，即壓縮後資料檔案空間的總大小不會低於82089.5 MB 。因此要將該資料檔案空間的總大小縮小到90000 MB，可執行如下命令：

  DBCC SHRINKFILE(1, 90000)

查看已使用日誌空間

回收日誌空間比較簡單，使用DBCC SQLPERF(LOGSPACE)命令或自治服務查看資料庫的記錄檔空間中實際已使用部分的比例。如果已使用部分的比例較高，收縮記錄檔幾乎沒有效果，可以查詢系統檢視表sys.databases，通過log_reuse_wait和log_reuse_wait_desc列的輸出資訊判斷空間為何無法回收。

收縮交易記錄

收縮完成後，您可前往RDS執行個體詳情頁監控與警示頁面，查看最新的日誌空間情況。

分析原因

臨時檔案空間是指系統資料庫tempdb佔用的空間大小。由於tempdb庫總是使用簡單復原模式，因此tempdb庫的記錄檔通常很小，但是資料檔案容易快速增長，例如建立大量暫存資料表、串連大表或排序等都可能導致tempdb庫資料檔案空間增加。

解決方案

• 盡量從資料庫應用程式層面規避，例如減少不必要的暫存資料表、大表串連查詢、避免大事務等。

• 在業務低峰期重啟RDS執行個體，重啟後tempdb庫佔用的空間會恢複到執行個體建立時的大小。

分析原因

其他檔案空間在此指sqlserver.other_size、mastersize、modelsize、msdbsize等佔用的空間大小。這些檔案通常都很小，但是某些情況下佔用的空間會很大，例如：

• 錯誤記錄檔errorlog較多，錯誤記錄檔檔案大小增長到幾GB甚至更大。

• 嚴重異常時自動產生的記憶體轉儲（memory dump）檔案。

解決方案

1. 您可以在RDS執行個體詳情頁監控與警示頁面的標準監控頁簽下，查看這些檔案所佔用的空間大小。各類指標含義，請參見查看標準監控。

   

2. 若errorlog佔用較大，您可在日誌管理頁面對錯誤記錄檔進行清理。具體操作，請參見管理日誌。

   說明

   若出現其他檔案（例如sqlserver.other_size）佔用過大的情況，請聯絡技術支援人員，技術支援人員會協助您鎖定原因並處理。