Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Orphaned subscription Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 4:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 2:56 PM
Points: 8, Visits: 95
we have restored an older database and with it came a subscription to a publication that we cannot seem to delete.

If we attempt to delete the subscription through SSMS an error pops up:
"'subscriber name' is not defined as a subscriber for 'server name'
could not update the distribution database subscription table. the subscription status cound not be changed."

there is not record for this subscription in the mssubscriptions table.

have tried various versions of sp_dropsubscription to no avail. we even get a completion message of "the subscriber was dropped" but, it does not go away.

Recreating the subscription from the subscriber side comes back with a message that the subscription already exists.

Trying to avoid deleting the publication as there will be recovery work to do on the application that uses the publication.

any help would be appreciated.
Post #1505442
Posted Monday, October 28, 2013 10:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:51 PM
Points: 2, Visits: 25
I believe that your problem to this link can help you.

http://www.mssqltips.com/sqlservertip/2710/steps-to-clean-up-orphaned-replication-settings-in-sql-server/


Hugs.
Post #1509045
Posted Wednesday, January 08, 2014 6:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:34 PM
Points: 19, Visits: 91
Depending on the subscription type, it is likely you will need to execute a stored proc on the subscriber to clean up the metadata there. Eg for merge:

NOTE: These are to be executed on the subscriber database:
exec sp_mergesubscription_cleanup @publisher = N'publisher sql instance name', @publisher_db = N'publication db', @publication = N'publication name'

And then a general cleanup one:
exec dbo.sp_MSdrop_mergesystables

Post #1529180
Posted Friday, January 10, 2014 12:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 2:56 PM
Points: 8, Visits: 95
Thanks all for the suggestions. What worked in the end was comparing the mssubscriptions table with a known good configuration and then manually adding in a record for the orphaned subscription.

Once there, the subscription could be successfully deleted.
Post #1529927
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse