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

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

元の製品バージョン: 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 データベースに対するバックアップ操作は、このデータベースで変更の追跡を有効にした場合に失敗します

• 修正: データベースで変更追跡を有効にした場合、2008 SQL Server、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.mdf ファイルにサービスごとの SID フル コントロールを付与するmssqlsystemresource.ldf必要があります。 これを行うには、次の手順を実行します。

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