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

發行項
03/07/2024

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

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

徵兆

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

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

偵錯與緩解

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

自動清除狀態

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

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.評估自動清除待辦項目

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

取得無效的清除版本：
```
SELECT * FROM sys.change_tracking_tables;
```
從資料列傳回的 cleanup_version 值代表無效的清除版本。

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

SELECT 'SELECT ''' + QUOTENAME(name) + ''', count(*) 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;

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

重要

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

針對側邊資料表執行下列緩解步驟，依照遞減順序排列過期資料列計數，直到過期資料列計數達到可管理狀態，才會趕上執行自動清除。

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

透過使用適當的值取代參數範本，以使用下列 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(*)
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(*) 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 分鐘

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

對於 SQL Server 和 Azure SQL 受控執行個體，請以比預設 30 分鐘更短的間隔，使用 sp_flush_CT_internal_table_on_demand 建立背景作業。對於 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;

共用方式為

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

徵兆

偵錯與緩解

自動清除狀態

1.清除已關閉

2.清除已開啟，但並未執行

自動清除執行，但未在進行中

1.評估自動清除待辦項目

2.檢查資料表鎖定衝突

3.檢查其他原因

對 syscommittab 問題進行疑難排解

清除期間的高 CPU 使用率

執行清除的頻率超過 30 分鐘

意見反應

其他資源

共用方式為

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

徵兆

偵錯與緩解

自動清除狀態

1.清除已關閉

2.清除已開啟，但並未執行

自動清除執行，但未在進行中

1.評估自動清除待辦項目

2.檢查資料表鎖定衝突

3.檢查其他原因

對 syscommittab 問題進行疑難排解

清除期間的高 CPU 使用率

執行清除的頻率超過 30 分鐘

相關內容

意見反應

其他資源