Adding a article causes ALL articles to be refreshed

Amir5656

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 or drop 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

Thanks,

Amir

Hilary Cotter


This is the default behavior for anonymous subscribers. IIRC through the GUI for named subscribers a complete snapshot will be generated. However if you use sp_addarticle only the mini snapshot for the one table will be generated.

Amir5656

I have tried using sp_article. The problem is that 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.

BTW, What does IIRC mean

Thanks,

Amir

Amir5656

Any more thoughts on this If my replication is setup and running, is there NO WAY to add "one" table to the publication without having to do the Bulk copy on ALL tables Adding a table to replication seems one of the most basic maintenance tasks. I'm still surprised that MSFT has made that basic task so difficult in 2005.