Replication - Adding article to a publication causing the snapshot of all other articles

Question

Adding an article(table) to a publication, and then running the snapshot agent should create a snapshot only for the new article. However, sometimes replication decides to re-snapshot every article in the publication. So, I have 2 questions:

Why sometimes all articles are re-snapshotted if only one article was added?
Is it possible to know beforehand which articles are marked to be snapshotted next time the snapshot agent runs (such as in a DMV or system table) ?

Thanks a lot.

score 0 · Answer 1 · answered Jul 31 '13 at 08:14

0

If memory serves, this behavior is governed by two settings on the publication: immediate_sync and allow_anonymous. In order to get the behavior that you want (i.e. add one article and have the snapshot be of one article), both of those settings need to be false. Luckily, you can find the value of both in the syspublications table in the published database and change them with sp_changepublication

answered Jul 31 '13 at 08:14

Ben Thul

31,080
4
45
68

Both immediate_sync and allow_anonymous are false. Any other ideas? – Lab Jul 31 '13 at 23:10
Unfortunately, no. When I've encountered this problem in the past, it's been those settings. – Ben Thul Aug 01 '13 at 23:26

score 0 · Answer 2 · answered Jan 04 '14 at 08:18

It seems weird, but I have seen this when adding an article and the solution was to cancel the article addition as it was asking to create a full snapshot. Then run the snapshot agent and it should find it has nothing to do, then add the article. Weird I know, but after 7 years of replication experience I have found this has worked everytime for me. Ben Thul is also correct on immediate_sync according to BOL.

Replication - Adding article to a publication causing the snapshot of all other articles

2 Answers2