February 16, 2005 at 5:18 am
A few months ago we had a server crash and lost a test database that had been replicated. The database was replicated using transactional replication and had a couple of different subscriptions that were replicating to different databases on a test server.
During the crash the database that was being replicated was damaged and the decision was made not to mess with it.
My problem is that the transactional publications are still there and I can't drop them because EM says Error 21766...The name 'test' was not found in the TransPublications collection....
Is there a way I can manually delete the fragments of these failed transactional replication databases?
Just looking for some way of cleaning up the mess.
I should note - We have other databases that are being replicated that are in use so I cant really just start over..
Thanks!
Jerry
February 16, 2005 at 6:09 am
This may or may not be the kosher way, but....
I've had similar issues using merge replication. You could try either of the following:
1) If you have a publication script, you can re-run it, re-creating the publication and then simply delete it. I have done that successfully a couple times
2) When in EM you can select from the menu Tools -> Replication -> Configure Publishing, Subscribers, Distribution... Select the Publication Databases tab and de-select the database. (Oddly enough, sometimes you need to select it first, hit Apply and then deselect it again) Note: in the Console you must have selected an object at Subscription level at least in order for the Replication option to be enables in the menu.
That should help you to clean up any unwanted publications.
You can then delete any unwanted db's.
Five Stones IT Consulting and Development
February 17, 2005 at 6:40 am
I've tried "tricking" it like that, but when I de-select it in EM, it still does not drop the 2 subscriptions that are showing in repl monitor.
If I try to recreate the publications with the same names as the ones that are showing in the replication monitor sql reports an error [14005] Could not drop publication a subscription exists to it.
February 17, 2005 at 7:34 am
I might not quite be following what you need. Is it the publisher or the subscriber you are trying to drop?
Five Stones IT Consulting and Development
February 22, 2005 at 1:48 am
Hi Jerry,
Could you provide a little bit more information on your setup.
Are you saying that your crashed database is still replicating and that you wish to drop the publications that exist on this crashed database?
Cheers..Graeme
February 23, 2005 at 5:57 pm
No, the original published database was corrupt and could not be restored from backup. So the publications for that db are still there.
At some point we restored an out of date backup of the published database and so this leaves us with a database (That is junk) and 2 publications (That are also junk)
When I try to delete the publications they report that they can't be dropped because there are subscriptions.
The reality of it is that there are no existing subscriptions to those publications, but SQL says otherwise (Somewhere)
? Sometimes it does not make any sinse to me, I hope I am making it a little more clear to you who are trying to help ?
Thanks for the suggestions, we'll get it yet...
J2
February 24, 2005 at 1:36 am
Hi,
When you restored, did you use the keep_replication swith?
Also have you tried scripting the drop replication for the particular publications rather than trying to simply delete them in EM
Graeme
February 24, 2005 at 4:29 am
No, didn't use the Keep_Replication switch, we actually just restored a database that was backed up prior to the replication being turned on.
I scripted it when I originally created it, but when I try to use the script I get:
Server: Msg 14055, Level 11, State 1, Procedure sp_dropsubscription, Line 149
The subscription does not exist.
And that's true, it does not exist. But the replication Monitor group shows
A publication, with a snapshot, logreader, and subscription.
I know these are leftovers from somewhere, I just don't know how to get rid of them.
June 10, 2008 at 4:51 pm
Did you get the solution to this problem? I am having the same problem and wonder if you could let me know the solution
June 11, 2008 at 1:18 am
Have you looked at the system replication tables...the ones that store the publications and subcribers ?
June 20, 2008 at 1:39 am
Have you checked store procedures related to replication like sp_droppublication?
Regards Ramon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply