I have recently upgraded my remote distributor to 2008r2. I am getting complete system locks when attempting to generate a new snapshot in the day when we have added an article to the publication (we could do this in the past with very little issues).
I have checked
sp_Helppublication and we are using synchronization method 3 (concurrent), so no locks should be held until the very end.
I have been trying to following this link as I am only adding an additional table with 26 rows but don't want to reinitialize and have the whole snapshot delivered.
I attempted to run this code
EXEC sp_changepublication @publication = 'statics', @property = N'immediate_sync', @value = 'false' GO
but this is also locking up the system completely. I guess I will have to run in the middle of the night but thought that you should be able to run this as just a config change to the publication.
Also when I try the last step in the link to add it to the subscription
EXEC sp_addsubscription @publication = 'statics', @article = 'dbo.SupplierCorporatePayAccountLink_tbl', @subscriber = 'PEARLJAM', @destination_db = 'MLT-Replicated', @reserved='Internal'
I get the following error message
Msg 14058, Level 16, State 1, Procedure sp_MSrepl_addsubscription_article, Line 331 Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information.
After the snapshot, how can I just have the new article added to the subscriber?