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開銷，因此需要根據實際業務情況進行評估，建議只在大表上啟用資料壓縮。

• 整理索引片段

  索引片段率過高會導致資料查詢速率變慢、實際佔用的資料存放區空間增大。通過整理索引片段可有效提升查詢速度，並釋放不必要的儲存佔用。

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

  說明

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

  • 如需分析某個索引的頁內平均空閑空間比例，可以使用SAMPLED或DETAILED模式查詢系統檢視表sys.dm_db_index_physical_stats，然後參考結果集中avg_page_space_used_in_percent列的值。更多資訊，請參見sys.dm_db_index_physical_stats (Transact-SQL)。查詢過程會讀取大量索引頁，可能影響資料庫效能，請在業務低峰期操作。

  • 索引重建操作（Rebuild）

    最佳化效果較好，尤其適用於片段率較高情境，執行效率更高。預設情況下重建操作會鎖定對應表，企業版執行個體可通過設定參數ONLINE為ON避免長時間鎖表。

    重要

    對於較大的索引，重建操作可能導致資料庫儲存空間和日誌大小在短期內大幅增加。執行索引重建操作前，請確保RDS執行個體已至少預留了被重建索引大小2倍以上的可用儲存空間。

    • 查看RDS執行個體儲存空間：進入RDS執行個體詳情頁的基本資料頁面，在執行個體資源地區，查看執行個體總儲存空間大小和已使用儲存空間大小。

    • 可用空間不足時：可進行儲存擴容，擴容成功後無需重啟執行個體，系統將自動應用新增空間，立即生效。

    ALTER INDEX <IX_YourIndexName> ON <YourTableName> REBUILD WITH (ONLINE = ON);

    上述命令執行完成後，後台會非同步重新採集索引統計資訊，該過程需要一定時間，因此控制台頁面顯示的片段率可能不會立即更新。您可以單擊重新採集按鈕手動採集最新資料，待資料擷取完成後單擊匯出指令碼下載到本地確認索引片段率是否下降。

    

  • 索引重組操作（Reorganize）

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

收縮資料檔案

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

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

  -- 僅適用於SQL Server 2012及以上版本使用，使用前指定
  DECLARE @dbName NVARCHAR(128) = 'YourDBName'  -- 資料庫名稱
  DECLARE @fileName NVARCHAR(128)  -- 資料檔案名
  DECLARE @targetSize INT = 2000   -- 目標大小（MB）
  DECLARE @shrinkSize INT = 5120   -- 迴圈收縮時每次收縮的大小（MB），建議每次5GB
  DECLARE @currentSize INT         -- 當前大小
  DECLARE @freeSize INT            -- 空閑空間大小
  DECLARE @usedSize 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
      -- 擷取當前空間大小、空閑空間大小，已使用空間大小
      DECLARE @sql0 NVARCHAR(MAX) = N'
      USE [' + @dbName + '];
      SELECT
        @currentSize = (SUM(total_page_count) * 1.0 / 128),
        @freesize = (SUM(unallocated_extent_page_count) * 1.0 / 128)
      FROM sys.dm_db_file_space_usage
      WHERE database_id = DB_ID();'
  
      EXEC sp_executesql 
        @sql0,
        N'@currentSize INT OUTPUT, @freesize INT OUTPUT',
        @currentSize OUTPUT,
        @freesize OUTPUT
      
      PRINT '當前大小:' + CAST(@currentSize AS VARCHAR(10)) + 'MB'
      PRINT '空閑大小:' + CAST(@freeSize AS VARCHAR(10)) + 'MB'
  
      set @usedSize=@currentSize - @freeSize
      PRINT '已使用大小:' + CAST(@usedSize AS VARCHAR(10)) + 'MB'
  
      -- 檢查目標大小是否小於已使用空間大小
      IF @targetSize <= @usedSize
      BEGIN
          PRINT '設定目標大小過小，請重新指定大小，targetsize不能小於： ' + CAST(@usedSize AS VARCHAR(20)) + 'MB'
          BREAK
      END
  
      
      -- 如果達到目標大小則退出
      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 = 'USE [' + @dbName + '];DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
      PRINT '執行收縮: ' + @sql
      EXEC (@sql)
      
      -- 等待一段時間後繼續
      PRINT '等待 ' + CAST(@waitTime AS VARCHAR(10)) + ' 秒後繼續...'
      WAITFOR DELAY '00:00:10'
  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)命令或自治服務DAS查看資料庫的記錄檔空間中實際已使用部分的比例。如果已使用部分的比例較高，收縮記錄檔幾乎沒有效果，可以查詢系統檢視表sys.databases，通過log_reuse_wait和log_reuse_wait_desc列的輸出資訊判斷空間為何無法回收。

收縮交易記錄

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

分析原因

解決方案

RDS SQL Server資料庫包含資料檔案和記錄檔，其對應回收方法如下：

分析原因

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

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

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

解決方案

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

   

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

   說明

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