變更追蹤自動清除問題疑難排解

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體

本文説明如何對變更追蹤自動清除中所觀察到的常見問題進行疑難排解。

徵兆

一般而言，如果自動清除未如預期般運作，您會看到下列一個或多個徵兆：

• 一個或多個變更追蹤附屬資料表或 syscommittab 系統資料表的儲存體使用量較高。
• 側邊資料表（例如名稱以前置詞 change_tracking 開頭的內部資料表，如 change_tracking_12345），或 syscommittab，或兩者皆顯示超出設定保留期間的大量資料列。
• dbo.MSChange_tracking_history 資料表包含具有特定清除錯誤的項目。
• CHANGETABLE 效能隨時間而降低。
• 執行自動清除或手動清除時，會報告 CPU 使用量較高。

偵錯與緩解

若要找出變更追蹤自動清除問題的根本原因，請使用下列步驟來偵錯並緩解問題。

自動清除狀態

檢查自動清除是否已執行。 若要檢查此問題，請查詢相同資料庫中的清除歷程記錄資料表。 如果正在進行清理，則資料表會包含清理開始和結束時間的條目。 如果清除尚未執行，則資料表是空的，或包含過時項目。 如果歷程記錄資料表包含具有 cleanup errors 資料列中的 comments 標記的項目，則清除作業會因為資料表層級清除錯誤而失敗。

SELECT TOP 1000 * FROM dbo.MSChange_tracking_history ORDER BY start_time DESC;

系統會定期執行自動清除，預設間隔為 30 分鐘。 如果歷程記錄資料表不存在，則很可能表明自動清除從未執行。 否則，請檢查 start_time 和 end_time 資料行的值。 如果最新項目並非最近的項目，也就是數小時或幾天前的項目，則表明自動清除可能並未執行。 如果是這種情況，則請使用下列步驟進行疑難排解。

1. 清理功能已關閉

檢查是否已開啟資料庫的自動清除功能。 如果不是，請開啟該功能，並等候至少 30 分鐘再查看新項目的歷程記錄資料表。 然後，監控歷史記錄表中的進度。

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('<database_name>')

is_auto_cleanup_on 中的非零值表示自動清除已啟用。 保留期間值會控制變更追蹤中繼資料保留在系統中的持續時間。 變更追蹤保留期間的預設值為 2 天。

若要啟用或停用變更追蹤，請參閱啟用和停用變更追蹤 (SQL Server)。

2. 清理功能已開啟，但未運行

如果自動清除已開啟，自動清除執行緒可能會因為非預期的錯誤而停止。 目前，重新啟動自動清除執行緒的做法不可行。 您必須執行容錯移轉至次要伺服器 (或在沒有次要伺服器的情況下重新啟動伺服器)，並確認資料庫已啟用自動清理設定。

自動清理在運行，但沒有取得進展

如果一個或多個側邊資料表顯示儲存體使用量較大，或包含超過設定保留期間的記錄，請遵循本節中說明單邊資料表的補救措施的步驟。 如有必要，可以針對多個資料表重複相同的步驟。

1. 評估自動清理積壓項目

識別包含大量過期記錄待辦項目的側邊資料表，需要對它們執行緩解措施。 執行下列查詢，以識別包含大量過期記錄的側邊資料表。 請記得如示例所示替換範例腳本中的值。

1. 取得無效的清除版本：

   SELECT * FROM sys.change_tracking_tables;
   

   從資料列傳回的 cleanup_version 值代表無效的清除版本。

2. 執行下列動態 Transact-SQL (T-SQL) 查詢，這樣會產生查詢以取得側邊資料表的過期資料列計數。 使用在上一個步驟中取得的值取代查詢中 <invalid_version> 的值。

   SELECT 'SELECT ''' + QUOTENAME(name) + ''', COUNT_BIG(*) FROM [sys].' + QUOTENAME(name)
       + ' WHERE sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts <= <invalid_version>) UNION'
   FROM sys.internal_tables
   WHERE internal_type = 209;
   

3. 從先前的查詢複製結果集，並從最後一個資料列移除 UNION 關鍵字。 如果您透過專用管理員連線 (DAC) 來執行產生的 T-SQL 查詢，該查詢會提供所有關聯資料表的過期資料列數量。 此查詢可能需要很長時間才能完成，取決於 sys.syscommittab 資料表的大小和側邊資料表的數目。

   重要

   必須執行此步驟，才能繼續執行緩解步驟。 如果上一個查詢無法執行，請使用下一個指定的查詢來識別個別側表的過期資料列數。

針對側邊資料表執行下列緩解步驟，依照過期資料列計數的遞減順序進行，直到過期資料列計數降低到讓自動清除功能能夠管理的狀態。

當您識別出包含大量過期記錄的側邊資料表時，請收集側邊資料表的刪除語句延遲資訊，以及過去幾個小時每秒的刪除速率。 接下來，請考慮過時的資料列計數和刪除延遲，以評估清除側邊資料表所需的時間。

透過使用適當的值取代參數範本，以使用下列 T-SQL 程式碼片段。

• 查詢每秒清除速率：

  SELECT
      table_name,
      rows_cleaned_up / ISNULL(NULLIF(DATEDIFF(second, start_time, end_time), 0), 1),
      cleanup_version
  FROM dbo.MSChange_tracking_history
  WHERE table_name = '<table_name>'
  ORDER BY end_time DESC;
  

  您也可以針對 DATEDIFF 函式使用分鐘或小時細微性。

• 在側邊資料表中尋找過時的資料列計數。 此查詢可協助您找出待清除的資料列數目。

  在上一節的輸出中，顯示了使用者資料表的 <internal_table_name> 和 <cleanup_version>。 使用這些資訊，透過專用管理員連線 (DAC) 執行下列 T-SQL 程式碼：

  SELECT '<internal_table_name>',
      COUNT_BIG(*)
  FROM sys.<internal_table_name>
  WHERE sys_change_xdes_id IN (
          SELECT xdes_id
          FROM sys.syscommittab ssct
          WHERE ssct.commit_ts <= <cleanup_version>
  );
  

  此查詢可能需要一些時間才能完成。 如果查詢逾時，請計算過時的資料列數量，方法是找出資料列總數與目前活躍的資料列之間的差異，即尚未清理的資料列。

• 執行下列查詢來尋找側邊資料表中的資料列總數：

  SELECT sum(row_count) FROM sys.dm_db_partition_stats
  WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
  GROUP BY partition_id;
  

• 執行下列查詢來尋找側邊資料表中的使用中資料列數目：

  SELECT '<internal_table_name>', COUNT_BIG(*) FROM sys.<internal_table_name> WHERE sys_change_xdes_id
  IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts > <cleanup_version>);
  

  您可以使用清理速率和陳舊資料列數量來計算清理資料表的估計時間。 請考慮下列公式：

  清理時間（分鐘） = (過時列數) / (清理速率（分鐘）)

  如果可以接受完成資料表清理所需的時間，那麼請監控進度，並讓自動清理繼續進行。 如果條件不符合，請繼續執行後續步驟，以進一步解析。

2.檢查資料表鎖定衝突

判斷是否因為資料表鎖定升級衝突阻礙清除作業，使其一再無法獲取側邊資料表上的鎖定來刪除資料列。

若要確認鎖定衝突，請執行下列 T-SQL 程式碼。 此查詢會檢索問題資料表中的記錄，以確認是否存在多個條目顯示鎖定衝突。 分散在一段時間內的零星衝突不符合繼續執行緩解步驟的資格。 衝突應該是定期發生的。

SELECT TOP 1000 *
FROM dbo.MSChange_tracking_history
WHERE table_name = '<user_table_name>'
ORDER BY start_time DESC;

如果歷程記錄資料表在 comments 資料行中有多個項目，且值為 Cleanup error: Lock request time out period exceeded，則表明多次清除嘗試因為鎖定衝突或連續鎖定逾時而失敗。 請參考下列補救措施：

• 在有問題的資料表上停用並啟用變更追蹤。 這會導致清除為資料表維護的所有追蹤中繼資料。 資料表的資料會保持不變。 這是最快的補救措施。

• 如果無法執行上一個選項，請啟用追蹤旗標 8284，繼續在資料表上執行手動清除功能，如下所示：

  DBCC TRACEON (8284, -1);
  GO
  EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
  

3.檢查其他原因

清除滯後的另一個可能原因是 delete 陳述式執行速度太慢。 若要判斷原因是否如此，請檢查 hardened_cleanup_version 的值。 此值可以透過專用管理員連線 (DAC) 擷取至考慮中的資料庫。

執行下列查詢來尋找強化的清除版本：

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1004;

執行下列查詢來尋找清除版本：

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1003;

如果 hardened_cleanup_version 和 cleanup_version 值相等，請略過本節，然後繼續進行下一節。

如果這兩個值不同，則表示一個或多個側邊資料表發生錯誤。 最快速的緩解措施是在有問題的資料表上停用和啟用變更追蹤。 這會導致資料表中維護的所有追蹤中繼資料被清除。 資料表中的資料會保持不變。

如果無法執行上一個選項，請在資料表上執行手動清除。

對 syscommittab 問題進行疑難排解

本節描述如何對 syscommittab 系統資料表進行偵錯和緩解資料表問題的步驟，特別是當它使用大量儲存空間或累積大量過時資料列的情況下。

syscommittab 系統資料表的清除取決於側邊資料表的清除。 只有在清除所有側邊資料表之後，才能清除 syscommittab。 請確認執行了自動清除執行但未取得進展章節中的所有步驟。

若要明確地呼叫 syscommittab 清除，請使用 sys.sp_flush_commit_table_on_demand 預存程序。

注意

如果 sys.sp_flush_commit_table_on_demand 預存程序正在刪除大量資料列，則可能需要一些時間。

如 sys.sp_flush_commit_table_on_demand 文章的範例章節所示，這個預存程序會傳回 safe_cleanup_version() 的值，以及已刪除的資料列數目。 如果傳回的值似乎為 0，並且已開啟快照隔離，則清除作業可能不會刪除 syscommittab 中的任何內容。

如果保留期間大於一天，在全域啟用追蹤旗標 8239 之後，應該可以放心地重新執行 sys.sp_flush_commit_table_on_demand 預存程序。 當快照隔離關閉時，使用此追蹤旗標永遠是安全的，但在某些情況下可能不需要。

清除期間的高 CPU 使用率

可能會在舊版 SQL Server 上看到本節中所述的問題。 如果資料庫中有大量的變更追蹤資料表，且自動清除或手動清除作業會導致 CPU 使用率較高。 此問題也可能是因為先前章節中簡短提及的歷程記錄資料表所造成。

使用下列 T-SQL 程式碼來檢查歷程記錄資料表中的資料列數目：

SELECT COUNT(*) from dbo.MSChange_tracking_history;

如果資料列數目夠大，且沒有索引，嘗試新增以下索引。 使用下列 T-SQL 程式碼新增索引：

IF NOT EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_MSchange_tracking_history_start_time'
        AND object_id = OBJECT_ID('dbo.MSchange_tracking_history')
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_MSchange_tracking_history_start_time
    ON dbo.MSchange_tracking_history (start_time)
END

將清除的執行頻率提高到每 30 分鐘一次以上

特定資料表可能會發生高頻繁變動，而且您可能會發現，在 30 分鐘的間隔內，自動清除作業可能無法清除側邊資料表和syscommittab。 如果發生這種情況，您可以更頻繁地執行手動清除作業，以輔助該流程。

對於 SQL Server 和 Azure SQL 受控執行個體，請使用 sp_flush_CT_internal_table_on_demand，並設定比預設的 30 分鐘更短的時間間隔。 對於 Azure SQL 資料庫，可以使用 Azure Logic Apps 來排程這些作業。

以下 T-SQL 程式碼可用來建立一個作業，以協助清理變更追蹤的輔助資料表：

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database
-- Fetch the tables enabled for change tracking
SELECT IDENTITY(INT, 1, 1) AS TableID,
    (SCHEMA_NAME(tbl.Schema_ID) + '.' + OBJECT_NAME(ctt.object_id)) AS TableName
INTO #CT_Tables
FROM sys.change_tracking_tables ctt
INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id;

-- Set up the variables
DECLARE @start INT = 1,
    @end INT = (
        SELECT COUNT(*)
        FROM #CT_Tables
        ),
    @tablename VARCHAR(255);

WHILE (@start <= @end)
BEGIN
    -- Fetch the table to be cleaned up
    SELECT @tablename = TableName
    FROM #CT_Tables
    WHERE TableID = @start

    -- Execute the manual cleanup stored procedure
    EXEC sp_flush_CT_internal_table_on_demand @tablename

    -- Increment the counter
    SET @start = @start + 1;
END

DROP TABLE #CT_Tables;

相關內容

• 關於變更追蹤 (SQL Server)
• 變更追蹤函數 (Transact-SQL)
• 變更追蹤預存程序 (Transact-SQL)
• 變更追蹤資料表 (Transact-SQL)
• 顯示資料庫的資料和記錄空間資訊
• 疑難排解 SQL Server 中的高 CPU 使用量問題