The Trouble with Transactional Replication and large articles.

  • So the procs should continue to function, as long as they're still in place. We're moving a very large database to a different server, which of course doesn't affect the articles - tables and procs are all there - just the connection.

    Thanks, Liston

  • It may be possible to drop the article from the publication. Question is if after dropping article distributor still complain on commands for this article stuck at distributor. May be there is a way to clear the commands stuck at distributor and manually synchronizing the table Or may be when you drop article it is clever enough to clear the commands related to that article from distributor. I think all these situations needs to be tested.

  • I just want to thank you for your post

    We needed to reinitialize an expired subscription with 1.2M records over a WAN

    So I took your idea, create a new publication with the new subscription

    With tablediff command fixed the differences between server/tables

    Drop the old expired subscription


    Jose Hindi

  • This was a system I inherited, the articles were in alphabetical order so there were huge and small row count articles mixed together. I won't recommend such a grouping of articles, I prefer to group based upon velocity of change. As to BCP it is already used in the snapshots. In the article I mentioned that you have to understand the application, in my case it was business critical to capture the current activity - any data older that 1 hour didn't really matter.

    I used RedGate's data compare to synch the older data after replication was up.


  • Drop the article doesn't remove the bad commands at the distributor, as long as those commands are there replication won't begin. Dropping the publisher is the quickest method to clear the bad commands.

    Thanks for reading and commenting.

  • Hi Greg:

    You'll find replication is quite exact. Setting the pre_cmd='none' only avoids the snapshot and prevent the overlaying of the subscriber data but it by itself is insufficient to get replication back up. In this case I hads bad commands at the distributor that prevented replication from working - those commands have to be cleared out. Dropping the publication is the quickest method.

    Thanks for reading and comments.

  • Paul:

    Thanks for the tip - I'll have to test out table diff and see good it is.

  • Other method could be as follows.

    1. Drop article from publication

    2. Delete all command at distributor for that article as follows.

    save all rows in msrepl_commands before deleting in case some thing goes wrong.

    select * into Msrel_commands_bck from MSrepl_commands where article_id= (select article_id

    from MSarticles

    where article = 'article_name')

    Delete all commands at distributor for that article.

    delete MSrepl_commands where article_id= (select article_id from MSarticles

    where article = 'article_name')

    Now you can create a new publication and add article to that with synch type replication support only. Once done you can use tablediff to copy the missing rows from publisher to subscriber.



  • jei33 (5/18/2012)

    Nice article, thanks.


    if you have detected that huge article as the conflicting one, wouldn't be enough just to drop that article from the publication, instead of deleting the whole publication?

    Thanks in advance

    This point stuck out to me as well. In a case where your publication has over 360 articles, I don't understand why you'd want to drop the entire publication. Am I missing the point trying to be made here?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Thanks to Paul Millar for pointing this out!

    paul.millar (5/21/2012)


    Short of Redgate Data Compare, check this out for generating Tablediff command lines...has saved my bacon a few times, and is surprisingly quick.

    (I've got Red Gate tools (which are cool!), but can't install them on isolated customer boxes on the other side of restrictive VPNs or DMZ's - whereas Tablediff ships with all 2008+ sql)

  • There are commands at the distributor which need to be cleared before replication resumes, I've found dropping the publisher the quickest method to clear them all out - but I will test is just dropping the article clear that articles's bad commands.

    Thanks for the question.

  • Excellent article, nice to know the info is there if I need it.

    One question, did you use this on SQL 2000 replication? (yes, companies out there are still using it!) :w00t:


    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]
  • This replication was on SQL 2005. Alas, my shop still has SQL 2000 despite my best efforts to upgrade. Highly recommend getting off SQL 2000.:-)

  • Edward.Polley 76944 (2/4/2013)

    This replication was on SQL 2005. Alas, my shop still has SQL 2000 despite my best efforts to upgrade. Highly recommend getting off SQL 2000.:-)

    Agree 100%, but trying telling that to paranoid IT managers, "It's production system, we best leave it alone!" :w00t: :crazy:


    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply