Change Tracking の自動クリーンアップに関する問題のトラブルシューティング

[アーティクル]
01/03/2024

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance

この記事では、Change Tracking の自動クリーンアップの際の一般的な問題をトラブルシューティングする方法を説明します。

現象

一般的に、自動クリーンアップが期待どおりに動作していない場合は、次の 1 つ以上の現象が見られます。

1 つ以上の Change Tracking サイドテーブルまたは syscommittab システムテーブルによる Storage の高消費量。
サイドテーブル (例えば、change_tracking_12345のように、名前がプレフィックスchange_trackingで始まる内部テーブル) またはsyscommittab両方のテーブル) には、構成された保持期間以外での膨大な数の行が表示されます。
dbo.MSChange_tracking_history テーブルには、特定のクリーンアップエラーを含むエントリがあります。
CHANGETABLE パフォーマンスは、時間が経過するにつれて低下しています。
自動クリーンアップまたは手動クリーンアップでは、CPU の高消費量が報告されます。

デバッグおよび軽減策

Change Tracking の自動クリーンアップに関する問題の根本原因を特定するには、次の手順に沿って問題をデバッグして軽減してください。

自動クリーンアップの状態

自動クリーンアップが実行中であるかどうかをチェックします。これをチェックするには、同じデータベース内にあるクリーンアップ履歴テーブルをクエリします。クリーンアップが実行中であれば、テーブルには、クリーンアップの開始時刻と終了時刻が適用された入力が存在します。クリーンアップが実行中ではない場合、テーブルは空、もしくは古いエントリとなります。履歴テーブルの列 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内の0 以外の値のとき、自動クリーンアップが有効であることを意味します。保持期間の値により、Change Tracking のメタデータがシステム内での保持期間が管理されています。 Change Tracking の保持期間の既定値は 2 日間です。

Change Tracking を有効または無効にするには、Change Tracking の有効化と無効化に関する記事をご覧ください。

2. クリーンアップは有効だが実行中ではない

自動クリーンアップ設定がオンでも、予期しないエラーが原因で自動クリーンアップスレッドが停止する可能性があります。今現在、自動クリーンアップスレッドを再度起動させる方法はありません。セカンダリサーバーへのフェールオーバーを開始 (セカンダリがない場合はサーバーを再起動)してデータベースの自動クリーンアップ設定が有効であることを確認してください。

自動クリーンアップは実行中されているが、進行していない

1 つ以上のサイドテーブルでストレージの高消費量が示されている、または、構成済み保持期間を超える多数のレコードが含まれている場合は、単一のサイドテーブルの解決策に関する説明が記載されているこのセクションの手順に従ってください。必要に応じて、それ以外のテーブルにも同じ手順を繰り返すことができます。

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 クエリを専用の管理者接続 (DAC) を使用して実行すると、クエリがすべてのサイドテーブルの有効期限切れの行数を返します。 sys.syscommittab テーブルサイズやサイドテーブルの数によって、このクエリが完了するまでに時間がかかる場合があります。

重要

軽減策の手順を進める上でこの手順が必要となります。前のクエリの実行に失敗した場合は、個々のサイドテーブルの有効期限切れの行数を次に指定したクエリを使用して特定します。

自動クリーンアップが追いつけるようにするには、有効期限が切れた行数の順序を減らすサイドテーブルに対して次の軽減手順を有効期限切れの行数が管理可能な状態になるまで実行します。

有効期限切れレコード数が多いサイドテーブルを特定したら、まず、サイドテーブルの削除ステートメントの待機時間、および過去の数時間の 1 秒あたりの削除率に関する情報を収集します。次に、古い行数と削除の待機時間とを両方考慮してサイドテーブルをクリーンアップするのに必要な時間を見積もります。

パラメーターテンプレートを適切な値に置き換え、次の T-SQL コードスニペットを適用します。

1 秒あたりのクリーンアップ率をクエリします。

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 を持つエントリが複数ある場合は、ロックの競合またはロックタイムアウトが連続して発生したために複数のクリーンアップの試行が失敗したということです。以下の対処法を検討してください。

問題のあるテーブルの Change Tracking を無効にして有効にします。これにより、テーブルで維持されるすべての Tracking メタデータが消去されます。テーブルのデータは手つかずでそのまま残ります。これが、最も迅速な解決策となります。
前のオプションが可能ではない場合は、次のようにして、トレースフラグ 8284 を有効にして、テーブルで手動クリーンアップを実行します。
```
DBCC TRACEON (8284, -1);
GO
EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
```

3. その他の原因をチェックする

クリーンアップのタイムラグ（遅延）のもう 1 つの原因は、遅延ステートメントの遅さです。そうであるかどうかを判断するには、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の値が等しいときは、このセクションをスキップして、次のセクションに進んでください。

両方の値が異なるときは、1 つ以上のサイドテーブルでエラーが発生したことを意味します。最も迅速な軽減策は、「問題のあるテーブルで Change Tracking を有効にする」を無効にすることです。これにより、テーブルで維持されるすべての Tracking メタデータが消去されます。テーブル内のデータは手つかずでそのまま残ります。

前のオプションが可能ではない場合は、テーブルで手動クリーンアップを実行します。

syscommittab の問題のトラブルシューティング

このセクションでは、システムテーブルで大量のストレージ領域を使用している場合、または古い行の大きなバックログがある場合に、syscommittabシステムテーブルの問題をデバッグして軽減する際の手順について説明します。

syscommittabシステムテーブルのクリーンアップは、サイドテーブルのクリーンアップに応じて異なります。すべてのサイドテーブルがクリーンアップされた後でのみ、syscommittabを削除できます。「自動クリーンアップは実行中されているが、進行していない」セクションのすべての手順が実行されていることを確認します。

syscommittabクリーンアップを明示的に呼び出すには、sys.sp_flush_commit_table_on_demand を使用します。

Note

行の大きなバックログを削除している場合、sys.sp_flush_commit_table_on_demandストアドプロシージャの時間がかかる場合があります。

sys.sp_flush_commit_table_on_demand のセクション例に示すように、このストアドプロシージャは、safe_cleanup_version()の値と削除された行数を返します。返された値が0と表示され、スナップショット分離がオンになっている場合、クリーンアップにより、syscommittabから何も削除されない可能性があります。

保持期間が 1 日を超える場合に、トレースフラグ 8239 をグローバルに有効にした後でsys.sp_flush_commit_table_on_demandストアドプロシージャを再実行することは安全です。スナップショット分離がオフのときに、このトレースフラグを使用することは常に安全ではありますが、必ずしも使用する必要がない場合もあります。

クリーンアップ中に CPU の使用率が高い

このセクションで説明される問題は、旧バージョンの SQL Server で生じる可能性があります。データベース内に Change Tracking テーブルが多数存在し、自動クリーンアップまたは手動クリーンアップによって 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 Managed Instance の場合は、既定の 30 分より短い間隔sp_flush_CT_internal_table_on_demandを使用してバックグラウンドジョブを作成します。 Azure SQL の場合、Azure Logic Apps を使用してこれらのジョブをスケジュールすることができます。

次の T-SQL コードを使用して、Change Tracking のサイドテーブルをクリーンアップするジョブを作成することができます。

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

Change Tracking の自動クリーンアップに関する問題のトラブルシューティング

現象

デバッグおよび軽減策

自動クリーンアップの状態

1. クリーンアップ設定が無効（オフ）になっている

2. クリーンアップは有効だが実行中ではない

自動クリーンアップは実行中されているが、進行していない

1. 自動クリーンアップバックログを評価する

2. テーブルロックの競合をチェックする

3. その他の原因をチェックする

syscommittab の問題のトラブルシューティング

クリーンアップ中に CPU の使用率が高い

クリーンアップを 30 分以上の頻度で実行

フィードバック

フィードバック

その他のリソース

Change Tracking の自動クリーンアップに関する問題のトラブルシューティング

現象

デバッグおよび軽減策

自動クリーンアップの状態

1. クリーンアップ設定が無効（オフ）になっている

2. クリーンアップは有効だが実行中ではない

自動クリーンアップは実行中されているが、進行していない

1. 自動クリーンアップ バックログを評価する

2. テーブル ロックの競合をチェックする

3. その他の原因をチェックする

syscommittab の問題のトラブルシューティング

クリーンアップ中に CPU の使用率が高い

クリーンアップを 30 分以上の頻度で実行

関連するコンテンツ

フィードバック

フィードバック

その他のリソース

1. 自動クリーンアップバックログを評価する

2. テーブルロックの競合をチェックする