SQL Server Performance

Adding a article causes ALL articles to be refreshed/reloaded

Discussion in 'SQL Server 2005 Replication' started by ac_786, Sep 6, 2007.

  1. ac_786 New Member

    I have setup Transactional replication in SQL 2005 between two servers and have about 200 tables being replicated. The problem is that every time, I add a table to replication, and start the Snapshot agent, it re-initializes every article and re-loads every article. This process takes 1 hour to complete and CPU usage goes to 100% during that time. This behaviour seems very different from SQL 2000 where I would start Snapshot agent and only the relevant tables were added/dropped. Has that functionality changed from 2000 to 2005? Am I not doing something right?
    If I try to use sp_addarticle to add the table instead of SSMS, then it gives me the error: "Cannot make the change because a snapshot is already generated. Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot".
    And in order to avoid the error, if I use the Option of @force_invalidate_snapshot=1, then again I am back to where I was before and it re-initializes everything. Is there NO WAY to add "one" table to the publication without having to do the Bulk copy on ALL tables?
  2. satya Moderator

    Check the publication properties if the snapshot gets invalidated whenever a change is made on it
  3. ac_786 New Member

    I looked at Properties of the Publication, Subscription and all articles and could not find any property that could be set to ensure that the snapshot did not get invalidated when a change is made. The proc Sp_addarticle has a property @force_invalidate_snapshot, but that is used to invalidate the snapshot. And like I said, if I try to add a article without setting that property, it throws out that error message.
    Please help !
  4. ndinakar Member

    How are you adding the article?
    I have added articles with this script without any issues:
    exec sp_addarticle @publication = 'Pub_Name'
    , @article = 'Article_Name'
    , @source_table = 'Article_Name'

    exec sp_addsubscription @publication = 'Pub_Name'
    , @article = 'Article_Name'
    , @subscriber = 'subscriber_server'
    , @destination_db = 'subscriber_db'
  5. ac_786 New Member

    As soon as I execute the first statement (sp_addarticle) I get the error: Cannot make the change because a snapshot is already generated. Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.
    Is there a property (like was mentioned before) that I can set to allow me to add articles without invalidating snapshots? May be during the setup phase I chose a wrong option and it always keeps invalidating the Snapshots.
  6. satya Moderator

    I believe this was a restriction on MERGE replication, but not with transactional as per your explanation. FYI BOL confirms that:
    To delete an article from a snapshot or transactional publication
    1. Execute /msdn.microsoft.com/mshelp" />sp_droparticle (Transact-SQL) to delete an article, specified by @article, from a publication, specified by @publication. Specify a value of 1 for @force_invalidate_snapshot.
      • (Optional) To remove the published object from the database entirely, execute the DROP <objectname> command at the Publisher on the publication database
    • On your question in MSDN forums about IIRC, that means "If I Remember Correctly" [:)].
  7. ColSchmoll New Member

    I got exactly the same problem in 2005, I can't add an article to my transactional replication without the snapshot agent creating a new snapshot for the whole publication. Can anyone advise?
  8. satya Moderator

    I wanted to know what is the service pack level on all those instances.
  9. ColSchmoll New Member

    I get this on Version 9.0.3159 and 9.0.3054, is this a bug?
  10. satya Moderator

    For a better dealing all the instances involved here must be with same level of service pack, try to keep it same.
  11. ColSchmoll New Member

    They are they same on both publisher and subscribers, I'm getting the problem on 2 different servers. Infact even if I replicate to a database on the same server I get this issue. Any ideas?
  12. ColSchmoll New Member

Share This Page