SQLServerWiki

“The Only Thing That Is Constant Is Change”

Distribution agent is skipping transactions at the subscriber.

Posted by database-wiki on April 11, 2016

Symptoms

ErrorCode = ‘20598’
ErrorText = ‘The row was not found at the Subscriber when applying the replicated command.’

Cause

Incorrect steps were followed when setting up subscriber using @sync_type = N’initialize with backup’. As per BOL, this can’t be done using GUI and can only be done using TSQL scripts.

Using TSQL scripts will ensure new commands in the LOG after the backup is taken will be queued in the Distribution DB and Replicated to the Subscriber.

Resolution

To resolve the issue, Create a new publication and set allow “allow_initialize_from_backup” to true on the Subscription options for the publication properties .

If the backup is used, you should use the TSQL to create the subscription. When executing sp_addsubscription you have to mention the backup file used to restore using @backupdevicename. The Dist agent will then execute a “Restore headeronly” command against the backup and will get the lastLSN from the file. The dist agent will then query the msrepl_transactions from that LSN forward and replicate all the changes to the subscriber.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: