Txn Replication sp_addarticle requesting @force_invalidate_snapshot = 1

Question

Txn Replication sp_addarticle requesting @force_invalidate_snapshot = 1

Express12

SSCoach

Points: 18514
More actions
February 28, 2006 at 11:08 am

#93426

Using SS 2K, I want to ADD 2 articles to an existing Publication (txn'l / continuous) WITHOUT having to regenerate the entire Snapshot which takes 4 hours. I've followed instructions in a few articles and have run into a few roadblocks.
1) I DTS the 2 new Articles from the source DB to the target DB (and create the PK's, indexes etc.)
2) I exec sp_addarticle using identical attributes from my other Articles in the Publication (as per Microsoft's instructions to script out the Publication and copy model syntax) The sp_addarticle FAILS w/ this msg:
Server: Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 54
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.
When I incorporate the @force_invalidate_snapshot = 1, the process works BUT it then invokes a FULL Snapshot of all Articles.
Is there any way to avoid generating the FULL Snapshot?

BT

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jen-169507 Right there with Babe Points: 776 More actions · Answer 1

actually, just add the articles, reintialize the subscriptions, then only those articles modified/added will be included in the snapshot to be generated

pretty straightforward, either use QA or EM, same result...

HTH

Express12 SSCoach Points: 18514 More actions · Answer 2

Thanks for the feedback -- here's exactly what I did

Using E/M, I added the 2 articles and yes, SS 2000 alerted me that this requires a reinitialization of the subscritpion -- which then reinitialized ALL articles in the publication by taking a complete Snapshot-- NOT just the 2 new articles. This is what I'm trying to avoid.

Any additional thoughts are appreciated.

BT

Jen-169507 Right there with Babe Points: 776 More actions · Answer 3

in EM, did you tick the articles? it will actually reinitialize but only those that needs to be, if you haven't modified anything in the other articles only a snapshot of 2 articles will be generated (so used to it that I actually phrased it that way )

then when the snapshot is ready, the distribution agent will also show 2 tables being delivered to the subscribers

so what you are saying, is that all of the articles were generated for snapshot? so when monitor the replication agents, you will actually see all of the articles being locked up one at a time for the snapshot to be generated?

Express12 SSCoach Points: 18514 More actions · Answer 4

I didn't tick the existing articles -- all I did was add 2 additional articles. As I mentioned, E/M indicated a Reinitialization is required. I watched the Snapshot Agent reintialize ALL the articles - not just the 2 new ones -- You can see the keywords (paraphrasing here) "Snapshot completed Reinitialing 62 Artcles"

BT

Jen-169507 Right there with Babe Points: 776 More actions · Answer 5

odd...

check the publication properties if the snapshot gets invalidated whenever a change is made on it

Amirali Charania Valued Member Points: 65 More actions · Answer 6

I know this is a old post, but I have the "exact" same issue with SQL 2005. Was a solution to this problem found? I have SQL 2000 running in the exact configuration and it works great. In Snapshot agent, only the tables that are added get copied over, NOT the whole publication. But in 2005, I have the same issue as above.

I'm guessing there is a property that needs to be set or reset. Any idea on what property is and where?

Thanks,

Amir

ColSchmoll SSC Enthusiast Points: 166 More actions · Answer 7

For anyone else having this problem, please read;

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2140692&SiteID=1&mode=1

azimse_nsr SSC Veteran Points: 204 More actions · Answer 8

have your problem resolved. Please feel free to concat for any assistance:)

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 9

Amirali Charania (9/7/2007)
I know this is a old post, but I have the "exact" same issue with SQL 2005. Was a solution to this problem found? I have SQL 2000 running in the exact configuration and it works great. In Snapshot agent, only the tables that are added get copied over, NOT the whole publication. But in 2005, I have the same issue as above.
I'm guessing there is a property that needs to be set or reset. Any idea on what property is and where?
Thanks,
Amir

Were you able to resolve this in 2005? Please let me know