Delete a wrong push subscription.

Question

Delete a wrong push subscription.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
August 6, 2017 at 6:56 am

#332098

Hello,
Recently after a server migration can see that some of the wrong / unwanted subscription still shows up in the repl monitor. Though jobs are cleaned but still see the subscription. Those are stopped anyways but in replication monitor those are still visible.
Kindly suggest how to clean up the push subscription..without damaging the other subscriptions...specifically that alone.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 1

Using this approach I removed the stale publisher...but not sure how to clean the wrong entry (push subscriber) appearing in Repl Monitor. Though in subscriber side I do not see the subscription name visible but in repl monitor I still see the push subscription name visible which I want to clean.

USE Publisher_db

GO

DECLARE @publicationAS sysname;

DECLARE @subscriberAS sysname;

--enter yourpublication name

SET @publication =N'publication_name';

--enter subscribername

SET @subscriber =N'subscriber_server';

USE Publisher_db

EXECsp_dropsubscription

@publication =@publication,

@article = N'all',

@subscriber =@subscriber

,@ignore_distributor=1;

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

I ran the job to clean up expired subscription (sp_expired_subscription_cleanup), still I see the stale subscriptions

Any advise how to clean them from repl monitor?

Thanks.

Bart Lewandowski Ten Centuries Points: 1099 More actions · Answer 3

Hi,

The Replication Monitor is taking most of the data (subsriptions, latency, pending cmds) from Distributor. You ran cleanup with ignore_distributor=1.

What exactly you migrate Publisher only? Was the distributor remote one? Are you using same distributor as before migration?

If u migrate Publisher and old one is not needed anymore (no replication coming off the old one)... you can go to distributor server and right click on Replication->Distribution Properties it will open new window , choose on the left pane Publishers. There is probably old publisher mapped and configured to that distributor. IF that old publisher is not needed anymore u can remove it from there by use checkbox. It will remove all the info for GIVEN publisher (entire publisher).

BartL
Replication Blog

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

Hello,

Thanks for the response. The old publisher/subscriber are no longer visible under the Replication folders in SSMS. But in repl monitor I still see the old subscription. I thought it will cleanup through the cleanup subscription but it did not.

Secondly, yes, the distributor is remotely setup. All distributor databases are remotely hosted.

Thanks.

Bart Lewandowski Ten Centuries Points: 1099 More actions · Answer 5

yes then it means you still have information about old replication in remote Distributor. Try removing old publisher from remote distributor as I mentioned before. Just remember that it will remove entire replication from given publisher.

BartL
Replication Blog

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

Trust me, it is not there. The v first thing which I did was unchecking the old server and using the new server.

Thanks.

Bart Lewandowski Ten Centuries Points: 1099 More actions · Answer 7

old distribution db is still there? run select * from msdistribution_agents and check if old subscriptions are there.

BartL
Replication Blog

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

Since it was a migration, I removed the old server(publisher list) from the distributor list in dist server. But using the same dedicated db for the app.

Thanks much...u saved me..
I found the stale entry in this table. Deleting this entry..the issue is gone. No more entries in Repl Monitor. 🙂
select * from msdistribution_agents

Thanks.