SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Orphaned subscription


Orphaned subscription

Author
Message
dmike
dmike
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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.
jfreitas89
jfreitas89
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 33
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.
Michael Oberhardt
Michael Oberhardt
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 93
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


dmike
dmike
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search