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

• Lab500

  Grasshopper

  Points: 21

  More actions

  July 30, 2013 at 7:44 pm

  #395228

  Hi guys,

  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:

  1. Why sometimes all articles are re-snapshotted if only one article was added?

  2. 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.

• Ravid_ds

  Ten Centuries

  Points: 1149

  More actions

  July 30, 2013 at 10:38 pm

  #1637206

  Run sp_helppublication on publisher database and check values of immediate_sync and allow_anonymous. These should be set to false so that snapshot will be generated for selected articles.

  EXEC sp_changepublication

  @publication = '<publication_Name>',

  @property = 'allow_anonymous' ,

  @value = 'false'

  GO

  EXEC sp_changepublication

  @publication = '<publication_Name',

  @property = 'immediate_sync' ,

  @value = 'false'

  GO

• Lab500

  Grasshopper

  Points: 21

  More actions

  July 31, 2013 at 5:15 pm

  #1637614

  Both immediate_sync and allow_anonymous are already false, but I still see all articles sometimes (not always) being snapshotted.

  Any other suggestions?

  Thanks.

• Ravid_ds

  Ten Centuries

  Points: 1149

  More actions

  July 31, 2013 at 11:36 pm

  #1637648

  Can you post the result by executing sp_helppublication on publication database.

• Lab500

  Grasshopper

  Points: 21

  More actions

  August 1, 2013 at 6:44 am

  #1637756

  Here it is:

  restricted0

  status1

  task1

  replication frequency0

  synchronization method3

  immediate_sync0

  enabled_for_internet0

  allow_push1

  allow_pull1

  allow_anonymous0

  independent_agent1

  immediate_sync_ready0

  allow_sync_tran0

  autogen_sync_procs0

  retention0

  has subscription1

  allow_queued_tran0

  snapshot_in_defaultfolder0

  pre_snapshot_scriptNULL

  post_snapshot_scriptNULL

  compress_snapshot0

  ftp_addressNULL

  ftp_port21

  ftp_subdirectoryNULL

  ftp_loginanonymous

  allow_dts0

  allow_subscription_copy0

  centralized_conflictsNULL

  conflict_retention14

  conflict_policyNULL

  queue_typeNULL

  backward_comp_level40

  publish_to_AD0

  allow_initialize_from_backup0

  replicate_ddl1

  enabled_for_p2p0

  publish_local_changes_only0

  enabled_for_het_sub0

  enabled_for_p2p_conflictdetection0

  originator_idNULL

  p2p_continue_onconflict0

  allow_partition_switch0

  replicate_partition_switch0

  Do you see anything that would cause that?

  And is there a way to list all articles set to be snapshotted next time the stapshot agent runs?

  Thanks.