Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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!

share|improve this answer
    
this only applies to pre SQl 2012 versions. The @reserved flag has been deprecated –  DamagedGoods Mar 11 at 16:50
add comment

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

share|improve this answer
add comment

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;
share|improve this answer
    
so recreating the subscription would detect the previous article and only add the 1 table ? I DO NOT want to apply a whole snapshot as this takes 45 mins as wipes out all indexing and is visible on our live website –  DamagedGoods Jul 26 '12 at 10:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.