Aline Zemp


Hi all

I've got the following situation:

  1. setup up an merge publication (distributor and publisher at the same server)
  2. setup an anonymous subscription
  3. 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




Re: get rid of an anonymous subscription

L Zhou


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







Re: get rid of an anonymous subscription

Aline Zemp

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.






Re: get rid of an anonymous subscription

L Zhou

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






Re: get rid of an anonymous subscription

Aline Zemp

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.





Re: get rid of an anonymous subscription

L Zhou

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.






Re: get rid of an anonymous subscription

Aline Zemp

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





Re: get rid of an anonymous subscription

Mahesh Dudgikar - MSFT

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.