We help IT Professionals succeed at work.

Can't delete an old Subscription to an old Publication in Replication

lahousden
lahousden asked
on
1,291 Views
Last Modified: 2009-12-16
We use snapshot and transactional replication substantially.  Unfortunately we upgrade our servers regularly on an ongoing basis and move databases around when we do - the server names for the retiring servers are retired also and the new servers have new names.  I am stuck with the situation where one of our SQL Servers shows three Pull subscriptions to Publications on servers that no longer exist.  I am unable to delete these through the Enterprise Manager GUI, and unfortunately, the only stored procedures that are listed in the documentation that refer at all to dropping or deleting subscriptions require that they be run at the Publisher - this is not possible since the Publisher as stated does not exist any more!?  Help!
Comment
Watch Question

Kevin HillSr. SQL Server DBA

Commented:
look in syssubscriptions in the published database

Author

Commented:
I don't know if all this has changed with SQL 2005; we are still running SQL 2000, so that may be a factor.

I can't look in the published databases since the servers are gone - and in a couple of cases the databases were not rehabilitated on other servers, and so they don't exist at all now.  I am thinking there must be some information on the subscriber server (which is where I see references to these "phantom" subscriptions in Enterprise Manager), but I don't know which table the information would be in - there doesn't seem to be an obvious choice: syssubscriptions would appear to be it, but that is on the Publisher saying who is coming "here" for info: what I need, I guess, is the info on the Subscriber saying where we are going to get the info...
Sr. SQL Server DBA
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Kevin3NF,

You set me off down the path which I believe will get me out of this - I have found the information that is showing up as phantom subscriptions in a table called MSreplication_subscriptions.  I guess this table is created in a database that is the target of a subscription - so I had to look in all the databases on the server until I found one which had this table.

My plan now is this:

i) Make a note of all current subscriptions on the server with the "phantoms"
ii) Drop all current subscritions on the server
iii) Script all Publications on the server
iv) Make a note of all subscriptions to those Publications
v) Drop all those subscriptions
vi) Disable Replication for the server
vii) Backup the database
vii) If the MSReplication_... tables are still present, empty them (safer, I think, than dropping them)

do you think this might work? (...and do you think it is safe?...)
Kevin HillSr. SQL Server DBA

Commented:
I don't know your environment well enough to risk telling you if it is safe or not....

Script everything, backup everything...then start dropping things you know to be inaccurate.

General order for removing replication:

Subscriptions
Publications
Subscribers
Publishing from the server that started it all.

Pretty much the reverse of how it gets set up

Author

Commented:
Thanks, Kevin3NF - I'll have to keep you in suspense as to how it goes: I can't try this until Sunday, when most users will be out of the system.  I'll let you know then.

Author

Commented:
Hi Kevin3NF - I basically followed my outline plan (with the implied putting everything back once I had emptied the tables) with one caveat.  After disabling Replication on the subscribing server (the one where the phantom subscriptions were showing up), I found a row in one of the MSsubscription_agents that related to a server that we still currently use, so I left that one in place - the other rows related to now missing servers and so I deleted them.

Everything is going smoothly now (and the phantom subscriptions no longer show up in Enterprise Manager) - I daresay that if I had just deleted the superannuated rows from the MSsubscription_agents and Msreplication_subscriptions tables in the first place (without turning off Replication first) that things would have turned out just the same - still, discretion is the better part of valour...

Thanks for your help and interest - hope you don't object to a "B" for this one...
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.