SQL Server の sys.syscommittab テーブルのキー行を複製する

この記事では、ファイル内の行が重複する可能性がある SQL Server Change Tracking の問題 sys.syscommittab 解決する方法について説明します。

元の製品バージョン: SQL Server 2008 以降のバージョン
元の KB 番号: 3083381

症状

Microsoft SQL Server でメモリ内 SYSCOMMITTABLE とディスク上の sys.syscommittab ファイルを比較すると、重複するキー行が表示されることがあります。 これらの値が重複すると、バックアップ操作とチェックポイント操作が失敗する可能性があります。

"一意のインデックス 'si_xdes_id' を持つオブジェクト 'sys.syscommittab' に重複するキー行を挿入できません。 重複するキー値は (KeyValue) です。
エラー: 3999、重大度: 17、状態: 1。
エラー 2601 が原因で、dbidDatabaseID のコミット テーブルをディスクにフラッシュできませんでした。 詳細については、エラーログを確認してください。

原因

この問題は、SQL Server の変更追跡の既知の問題が原因で発生します。

重複キーの原因となる要因を解決する

重複キーの原因となる要因を解決するには、状況に応じて、次のいずれかの修正プログラムを適用します。

• 修正: このデータベースで変更の追跡を有効にすると、SQL Server 2008 または SQL Server 2008 R2 データベースでのバックアップ操作が失敗する

• 修正: データベースで変更の追跡を有効にした場合、SQL Server 2008、SQL Server 2008 R2、または SQL Server 2012 でバックアップが失敗する

• 修正: 変更の追跡を有効にした後、SQL Server 2008、SQL Server 2008 R2、または SQL Server 2012 データベースでバックアップ操作が失敗する

これらの修正により、重複するキー行が表示され続けなくなりますが、重複する行は自動的に削除されません。 重複する行を削除しないと、影響を受けるデータベースはデータベース チェックポイントを完了できず、バックアップが失敗する可能性があります。

重複する行を削除するための変更の追跡を無効にして有効にする

1. 影響を受けるテーブルとデータベースの変更の追跡を無効にします。
2. データベースチェックポイントを手動で発行します。
3. 影響を受けるデータベースとテーブルで変更の追跡を有効にします。

変更の追跡の詳細については、「変更の追跡 を有効または無効にする」を参照してください。 手動チェックポイントの発行については、 CHECKPOINT (Transact-SQL) を参照してください。

重複する行を手動で削除する

1. Transact-SQL スクリプト セクションからテキスト エディターに Transact-SQL スクリプトをコピーします。
2. スクリプトで <AFFECTED_DB> プレースホルダーを見つけ、影響を受けるデータベースの名前に置き換えます。
3. 変更したスクリプトをハード ディスクに.sql ファイルとして保存します。 たとえば、C:\temp\remove_duplicates.sql のようにします。

SQL Server 2014 以降のバージョンを使用している場合は、 mssqlsystemresource.ldf ファイルと mssqlsystemresource.mdf ファイルにサービスごとの SID フル コントロールを付与する必要があります。 この手順を実行するには、以下のステップに従ってください。

1. インスタンス ID に対応する Bin ディレクトリに移動します。 例えば次が挙げられます。
   C:\Program Files\Microsoft SQL Server\<Instance ID>\MSSQL\Binn

2. mssqlsystemresource.ldfとmssqlsystemresource.mdfのプロパティを開き、[セキュリティ] タブを選択します。

3. サービスごとの SQL Server サービス SID を見つけ、既定のアクセス許可をメモします。

   • *Read & execute
   • *Read

4. SQL Server サービスにサービスごとの SID フル コントロールを付与し、アクセス許可ダイアログ ボックスを閉じます。

5. シングル ユーザー モードで SQL Server を起動します。 詳細については、「 シングル ユーザー モードで SQL Server を起動する」を参照してください。

   注

   SQL Server は、シングル ユーザー モードで起動すると、Always On 可用性グループ (AG) の一部であるデータベースのスタートアップ プロセスをスキップします。 シングル ユーザー モードで SQL Server を起動する必要がある変更の追跡に関する問題をトラブルシューティングする必要があり、変更追跡が有効になっているデータベースも AG の一部である場合は、データベースがオンラインになるように、シングル ユーザー モードで SQL Server を開始する前に AG からデータベースを削除する必要があります。

6. sqlcmd コマンド ラインを使用して、専用管理者接続 (DAC) で SQL Server に接続し、変更した Transact-SQL スクリプトを実行します。 例えば次が挙げられます。

   sqlcmd -S PRODSERV1\MSSQLSERVER -A -E -i c:\temp\remove_duplicates.sql
   

7. マルチユーザー モードで SQL Server を再起動し、影響を受けるデータベースに対するバックアップ操作と CHECKPOINT 操作が正常に完了したことを確認します。 手順 4 を使用した場合は、アクセス許可を既定値に戻します。

Transact-SQL スクリプト

--Create a temporary database to store the necessary rows required to remove the duplicate data 
USE master
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = 'dbChangeTrackingMetadata')
BEGIN
  DROP DATABASE dbChangeTrackingMetadata
END
GO
CREATE DATABASE dbChangeTrackingMetadata
GO

--Table to store the contents of the SYSCOMMITTABLE
USE dbChangeTrackingMetadata
GO
CREATE TABLE dbo.t_SYSCOMMITTABLE (
commit_ts BIGINT
,xdes_id BIGINT
,commit_lbn BIGINT
,commit_csn BIGINT
,commit_time DATETIME
)
GO

--Table to store the duplicate rows to be removed from the sys.syscommittab table

CREATE TABLE dbo.t_syscommittab (
commit_ts BIGINT
,xdes_id BIGINT
,commit_lbn BIGINT
,commit_csn BIGINT
,commit_time DATETIME
,dbfragid INT
)
GO

--Enable the usage of OPENROWSET
EXEC sys.sp_setbuildresource 1
GO

--Change <AFFECTED_DB> to the database that contains the duplicate values
USE <AFFECTED DB>
GO
DECLARE @rowcount BIGINT
SET @rowcount = 0

--Copy all rows from the SYSCOMMITTABLE INTo the temporary database
INSERT INTO dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE
SELECT commit_ts, xdes_id, commit_lbn, commit_csn, commit_time
FROM OPENROWSET (table SYSCOMMITTABLE, db_id (), 0, 0)

--Save the duplicate values INTo the temporary database
INSERT INTO dbChangeTrackingMetadata.dbo.t_syscommittab
SELECT ondisk_ct.* 
FROM sys.syscommittab as ondisk_ct
JOIN dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE as inmem_ct
 ON ondisk_ct.xdes_id = inmem_ct.xdes_id

--Delete the duplicate values
DELETE FROM sys.syscommittab
WHERE xdes_id in ( SELECT xdes_id from dbChangeTrackingMetadata.dbo.t_syscommittab )

SET @rowcount = @@rowcount

IF (@rowcount > 0)
BEGIN
  PRINT ''
  PRINT 'DELETED '+CAST(@rowcount as NVARCHAR(10))+' rows from sys.syscommittab that were also stored in SYSCOMMITTABLE'
PRINT ''
END
ELSE
BEGIN
  PRINT ''
  PRINT 'Failed to DELETE DUP rows from sys.syscommittab'
  PRINT ''
END
EXEC sys.sp_setbuildresource 0
GO