Transactional Replication snapshot locking Publisher db from beginning

Question

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.

http://deepakrangarajan.blogspot.co.uk/2009/01/sql-2005-transaction-replication-adding.html

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?

Answer 1

To get this to work I scheduled switching off the 'immediate sync' to very low traffic period

EXEC sp_changepublication  
@publication = 'statics',
@property = 'Immediate_sync',
@value = 'false'
GO 

I then ran a snapshot which only took a fraction of the time at 6 mins.

I then had to drop the current subscription for that article only

    EXEC sp_dropsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR'

and rerun the snapshot. Then i could add the subscription back into the publication

    EXEC sp_addsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR',
    @destination_db = 'MLT-Replicated',
    @reserved='Internal'

but it would not allow this without the fairly obscure setting

@reserved ='Internal' 

or I got an error message saying I needed to subscribe to all articles.... replication then automatically sync'd the new table. I'm a happy man as this has been causing me some long nights!

Answer 2

Sometimes this happens if some cleanup did not complete properly.

Run the drop subscription procedure on the subscriber to clean up.

For the case of Merge Replication look like this:

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'TheNameOfYoutPublicaction';
SET @subscriber ='thehostnameofyoursuscriber';
SET @subscriptionDB = N'TheSubscriptionDBThatWillDrop';

USE [YourPubDataBase]
EXEC sp_dropmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB;
GO

More info:

http://technet.microsoft.com/en-us/library/ms184385.aspx

http://technet.microsoft.com/en-us/library/ms176045.aspx

Answer 3

You need to remove the existing publication. Use the below code to guide you, how you can remove existing publications. I hope it works.

-- This batch is executed at the Publisher to remove 
-- a pull or push subscription to a transactional publication.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @subscriber = $(SubServer);

USE [AdventureWorks2012]
EXEC sp_dropsubscription 
 @publication = @publication, 
 @article = N'all',
 @subscriber = @subscriber;