get rid of an anonymous subscription

Hi all

I've got the following situation:

setup up an merge publication (distributor and publisher at the same server)

setup an anonymous subscription

deleted the subscription

In the replication monitor, I still see the deleted subscription.

Why?

And how do I get rid of it?

Thanks a lot for any informations and greetz

Aline

1. try to delete the subscription on the publisher side using sp_dropmergesubscription, (see http://msdn2.microsoft.com/en-us/library/ms176045.aspx) and

2. try to delete the subscription on the subscriber side using sp_dropmergepullsubscription, (see http://msdn2.microsoft.com/en-us/library/ms187336.aspx) if hte subscription is a Pull Subscription.

3. Restart the replication monitor.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

Unfortunetaly, I've already done this (with the management studio). But without getting in touch with the distributor.

I thought no metadata are stored at the distributor for an anonymous subscriber, but as I see it in the replication monitor there got to be some traces...

I'm still thankful for any hint, to get rid of these traces after deleting.

|||

Let me make sure

1. You have run the SP (sp_dropmergesubscription and sp_dropmergepullsubscription) manaully on the publisher/subscriber right (not going through the UI)? If not please run them manually.

2. Can you check the subscription by running select * from sysmergesubscriptions on the publication database?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

I've done both by now (dropping via UI and with sp_dropmergepullsubscription). And still have it on the replication monitor, even after a restart of the replication monitor.

The "select * from sysmergesubscriptions" on the publication database doesn't show any dropped subscriptions.

The problem on the productive system is even more complex. A subscriber db was deleted (without dropping the subscription properly) and a new subscription from this subscriber to the same publication was made. As a result, the subscription appeared two times on the replication monitor (some Laptops appear up to 4 times!!). Now, there seems to be also a performance problem. So I hoped, I first would tidy up a little bit.

So, what happens if I drop the subscription with sp_dropmergepullsubscription, I remove the still working subscription without removing it from the replication monitor. And this dilemma I already have...

Any other ideas?

Thanks for your help so far.

|||

Sorry for the late reply.

Can you try to run sp_replmonitorhelpsubscription on the distribution database, http://msdn2.microsoft.com/en-us/library/ms188073.aspx?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

sp_replmonitorhelpsubscription shows all subscriptions - the already dropped included.

I now realise, I need another approach. The new questions are:

After deletion of an anoymous pull subscriber database (without first dropping the subscription regularly dropped with sp_dropmergepullsubscription), how do I remove all traces of it at the distributor / publisher?

Can I force the cleanup to remove entries, which are waiting to be synchronized only to this deleted subscription?

Thanks for your help

Aline

|||

Hi Aline,

This is a bug.

Currently when you create an anonymous subscription and delete it with:

sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, the subscription is removed from the subscriber and publisher databases. However some trace is left behind in the distribution database. Now replication monitor reads this data from the distributor and hence this subscriptions still shows up there.

You can use the follwoing workaround/trick :)

On the same publication, pub database, and sub database, create a dummy PULL subscription. You dont need to synchronize. Then call sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, this time with the appropriate parameters saying that it is a pull subscription. This will clear the entry in the distribution database and your monitor will not show this subscription anymore.