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? Thanks, Amir
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 !
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'
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. Thanks, Amir
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 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" [].
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?
For a better dealing all the instances involved here must be with same level of service pack, try to keep it same.
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?
For anyone else having this problem, please read; http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2140692&SiteID=1&mode=1