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?

  • The issue was from the distributor to subscriber, which was on the same server (SQL Server 2012). Transactional replication. Jun 8, 2016 at 12:40

1 Answer 1


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'
EXEC sp_changepublication
@publication = 'REP_P', --Enter your publication name
@property = 'immediate_sync' , 
@value = 'false' 

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

  • Yes, but what if the updated was already executed and you are just troubleshooting after the fact? Would you still remove the table from replication and add it back? Jun 8, 2016 at 13:37
  • @Hpk IMO it may work when you have small table but still have to test it though. What if we have very large table. Re-snapshot would be pain in that case.
    Jun 8, 2016 at 13:40
  • 1
    @DerekCzarny - Its an alternative. In my environment unless I would have to prove I tested my method and prove data is intact to my Manager, it would also need to go through CAB etc due to the non standard process. I guess its up to your environment. My method is a sure fire way no questions asked that the publisher matches the subscriber. In terms of table size 33 million records is not large unless its very wide or contains large data types.
    – Hpk
    Jun 8, 2016 at 14:45
  • If you used distribution.dbo.msRepl_transactions and distribution.dbo.msRepl_commands to get the update commands, restrict down to the applicable article(s) then your method looks ok. xact_seqno is sequential and is the right thing to order by.
    – Hpk
    Jun 8, 2016 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.