Tagged: Line 533

Replication – Snapshot is not getting generated

Replication issues and resolutions

Today I have encountered very strange issue –

Scenario – Snapshot is not getting generated for newly added articles

or

Error when setting up Transactional Replication via SQL scripts.

Msg  14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Step performed to resolved the issue-

  1. Re snap with reinitialize all subscription option no luck.
  2. Removed the newly added articles and add back and ran the snapshot but NO Luck.
  3. Drop the subscriber and add back and generate the snapshot but no luck ( Most of time it resolve the issue )
  4. Script out publication and drop the publication and when I try to add

While adding Publication i am getting error message –

Error Message –

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Resolution —

There are 2 unofficial workarounds:

(a) You can circumvent the check by specifying @reserve = ‘internal’ when you add the subscription for the new article and the snapshot agent should generate snapshot for the new article

(b) You could change the immediate_sync property in syspublications to 0 (see sp_changepublication).

Other more official workarounds —

Including changing the sync_method from ‘concurrent’ to either ‘database snapshot’ (enterprise edition only) and ‘native’ (which locks table during snapshot generation). Change the sync_method will force a reinitialization of all your subscriptions at this point. Alternatively you could create another publication and use this instead.

Before —

use [Publisher db]

exec sp_addpublication @publication = N’Publicatoin name’,

@description = N’Transactional publication of database ”dirtrbuter name” from Publisher ”publisher name”.’,

@sync_method = N’concurrent’,

@retention = 0,

@allow_push = N’true’,

 

After —

use [Publisher db]

exec sp_addpublication @publication = N’Publicatoin name’,

@description = N’Transactional publication of database ”dirtrbuter name” from Publisher ”publisher name”.’,

@sync_method = N’native’,

@retention = 0,

@allow_push = N’true’,

 

  1. I have change the @sync_method = N’concurrent’ to @sync_method = N’native’ and ran the script to create publication.
  1. Ran the snapshot but snapshot not getting generated for newly added articles

7. Remove all article (from console) and added again … and ran the snapshot resolve my issue