Skipping records in SQL Server replication
Replication is down!😟 Now what?!
This happened to us today, one of our subscribers, decided to implement a few triggers on their subscriptions. These triggers were inserting data into other tables that had a few foreign keys to respect their data integrity.
The problem here is that for some reason one of these foreign keys failed, the entire replication got brought to a halt. No more data is delivered until the replicated transaction can complete. In our case, a deeper look had to be taken by our colleagues into the way foreign keys have been applied and we could not have the entire replication stop because of this( it only happened for a few records due to a change in natural keys done a while ago).
So, how can tell the distributor agent to ignore this transaction and just move on? Time for sp_setsubscriptionxactseqno.
sp_setsubscriptionxactseqno [ @publisher = ] 'publisher' , [ @publisher_db = ] 'publisher_db' , [ @publication = ] 'publication' , [ @xact_seqno = ] xact_seqno
This has to be run in the subscriber database.
❓ How do we find which records are causing replication to stop?
Our first option is to use our old replication monitor friend.
Here we can use the replication monitor to identify which log sequence number is causing the issue or we could have a look at subscription errors using sp_helpsubscriptionerrors.
sp_helpsubscriptionerrors [ @publisher = ] 'publisher' , [ @publisher_db = ] 'publisher_db' , [ @publication = ] 'publication' , [ @subscriber = ] 'subscriber' , [ @subscriber_db = ] 'subscriber_db'
We should get something like this:
It´s possible that you see past events in the output of this stored procedure. Take the time in consideration.
To understand what command we are really talking about, we can use sp_browsereplcmds. This will return a result set in a readable format of the replicate commands stored in the distribution database. This SP is executed at the distributor on the distribution database. The syntax of the procedure is:
sp_browsereplcmds [ [ @xact_seqno_start = ] 'xact_seqno_start' ] [ , [ @xact_seqno_end = ] 'xact_seqno_end' ] [ , [ @originator_id = ] 'originator_id' ] [ , [ @publisher_database_id = ] 'publisher_database_id' ] [ , [ @article_id = ] 'article_id' ] [ , [ @command_id= ] command_id ] [ , [ @agent_id = ] agent_id ] [ , [ @compatibility_level = ] compatibility_level ]
Don´t let this bite you. My first thought was that this had to be the database_id in sys.databases but it´s not. The publisher database id is coming from MSpublisher_databases (id field)
❓ I´ve run sp_setsubscriptionxactseqno on the subscriber, now what?
The next step is to stop and start the distributor agent again. The next time the agent starts, it will see that there´s a flagged transaction to be skipped and it will pick up from the next available transaction available.