背景

SQL Serverのトランザクションレプリケーションにおいて、エラーが発生する原因は複数存在します。
今回はその中で、「サブスクライバ側のテーブルを更新してしまった場合」の原因特定の方法について考えてみました。
自分で手動更新してしまった場合は、「どのクエリを更新したか」は分かりますが、誰がどのテーブルに対して更新を実施したか分からない状況を想定しています。

方法

１．サブスクライバ側で、エラーが発生したパブリケーションの最後に成功していたtransaction_timestamp(=xact_seqno)を取得

パブリケーション名も取得しておくと良いと思います。

select * from MSReplication_Subscriptions

２．該当のパブリケーションのアーティクルを含むxact_seqnoの中で、最後に成功していたxact_seqno以降のxact_seqnoを古い順に取得

取得できたxact_seqno＝失敗した可能性が高いxact_seqno＝レプリエラーの原因となったクエリ情報を取得できる可能性があると考えました。

--distributionDBで実行
--最後に成功していたxact_seqnoの次のxact_seqnoを取得 = 失敗していた可能性が高いxact_seqno
declare @error_publication_name varchar(1000) = 'my_publication'
select top 10 * from MSrepl_commands
where xact_seqno > 0x00653B43000150F0006B000000000000
and exists (
    select publication, * from distribution.dbo.MSarticles a join distribution.dbo.MSpublications b on a.publication_id = b.publication_id
    where a.article_id = MSrepl_commands.article_id
    and publication = @error_publication_name
)
order by xact_seqno

ポイントとしては、複数パブリケーションが混在している環境だと、「最後に成功したxact_seqnoの直後のxact_seqno=失敗したxact_seqno」とは限らないため、existsを使ってエラーが発生したパブリケーションのアーティクルを含むxact_seqnoに限定して抽出を行っている点です。

３．コマンドを解決

取得できた「怪しいxact_seqno」を使って、どういったコマンドを実行しようとしていたか解決します。
これにはsp_browsereplcmdsを使います。

exec sp_browsereplcmds @xact_seqno_start = '0x00653B490001A2B8009F', @xact_seqno_end = '0x00653B490001A2B8009F'

この例だと、MyTableのDELETEを実行しようとしてエラーとなっていると推測できます。
この場合は、サブスクライバ側にPK＝55485のレコードが存在しない（＝誤って手動削除した可能性）ためにエラーとなっており、該当のPKを持つレコードをサブスクライバ側のテーブルにINESRTしてあげればOKです。

複数レコードを更新してリカバリが困難な場合

この場合は、データを補正するよりも、エラーを起こしているテーブルが確認できた後は以下の手順をとるのもありだと思います。

１．該当のパブリケーションからエラーを起こしているテーブルを削除
２．パブリケーションに最後テーブルを追加
３．スナップショットエージェントを実行

この手順を踏むと、一時的にサブスクライバ側から追加したテーブルがDROPされることと、スナップショットエージェントの実行時にパブリケーションに含まれる全テーブルに一時的にSch-Mロックがかかるのでブロッキングの懸念がありますが、レプリ復旧の確実な手順となりますので覚えておいて損はないと思います。