SQL Server Replication

Question

We had an issue with replication, where a large (7.5 million row) update was done on a 33 million row table.  Replication converted that to 7.5 million individual update statements.  When my alerting notified me that our publication was behind our threshold, I started to examine the issue.

I discovered that the updated was executed and it would have taken a few days to chew through those update statements.  So I decided to see if we could skip over those records that it was trying to process.  Using the system tables and stored procedure I was able to determine the period of time that the updated was executed.  Once I found the last xact_seqno, I stopped the distribution agent, and did the update manually to the subscriber database.  I then executed sp_setsubscriptionxactseqno, to skip past all of those 7.5 million transactions.  When I started up the Distribution Agent, it seemed to have worked and was able to process the remaining transactions.

For good measure I use Redgate’s data compare to see if the data was messed up, but I was missing about 24 records (which could have been not there originally because I didn’t set it up).

My question is was that the right way to fix it?  How are you always assured to get the next Xact_seqno?  Do you order the transactions by Xact_Seqno or entry_time?

Hpk · Answer

An alternative would to remove the article from the publication and re add, causing a new snapshot to be taken of the table. This way there's no question whether the right data is in the subscriber.

To do this you need to make sure the publication properties are set to FALSE for allow_anonymous and immediate_sync - if either of these are true then the a database snapshot will be taken when you remove and re add table rather than just the table.

To check run sp_helppublication against the distribution.

To change if either of these are set to 1:

EXEC sp_changepublication
@publication = 'REP_P', --Enter your publication_name
@property = 'allow_anonymous' ,
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'REP_P', --Enter your publication name
@property = 'immediate_sync' , 
@value = 'false' 
GO

I have tested this and it works in my environment, however strongly suggest you do this in a lab first.

SQL Server Replication

One Answer

Add your own answers!

Ask a Question