Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Cleaning up Replication Monitor Expand / Collapse
Author
Message
Posted Friday, July 20, 2012 7:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 13, 2015 4:50 AM
Points: 2, Visits: 29
I have had similar problems on a sql2k replication subsystem. Removal of subscriptions from a publication deadlocked+killed, leaving orphaned subscriber info on the distributor.

Replication monitor reports the subscription from distribution database, but publication does not think there are any (until an error is thrown when attempting to delete).
Recreating the subscriber completed ok, but when attempting to delete the new the publication again the new subscription info was removed & an error thrown at the orphaned data again.

To resolve, I went to the distribution database on the distributor & checked for the relevant subscriber in mssubscriptions & found the article data from msarticles
this allowed me to successfully use sp_msdrop_subscription to remove the orphaned data.

After that removal of the publication was ok.
Post #1332940
Posted Friday, November 23, 2012 1:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 29, 2015 4:41 AM
Points: 3, Visits: 210
nuwins solution is Perfect, worked perfectly!
Post #1388245
Posted Wednesday, June 12, 2013 7:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 25, 2015 10:35 PM
Points: 16, Visits: 476
I Know it is bit late, but may help someone in future.

Delete related publication entries from following tables. This will clean up Replication monitor. Please make sure you delete the old publisher(which doesn't exist any more) and not the current one.

select * from distribution..MSpublications

-- delete from distribution..MSpublications where publication_id = **

select * from [distribution].[dbo].[MSsnapshot_agents]

-- delete from [distribution].[dbo].[MSsnapshot_agents] where id = **
Post #1462881
Posted Thursday, June 20, 2013 10:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 15, 2015 1:56 PM
Points: 14, Visits: 108
I just clean up the very old publication's from the replication monitor using this tables. Thanks!
Post #1466029
Posted Monday, August 4, 2014 8:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:02 AM
Points: 1, Visits: 1
you should delete a row in table [distribution].[dbo].[MSsnapshot_agents], the database distribution is in system's database.
Post #1599273
Posted Monday, January 26, 2015 10:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 12, 2015 5:48 AM
Points: 4, Visits: 12
adding a new publication with same name and removing it works . Finally i could delete all those red marked publications from the replication monitor !!
Post #1654936
Posted Saturday, February 21, 2015 3:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 26, 2015 6:51 AM
Points: 1, Visits: 21
So how it would be deleted from the Replication monitor? though every time i delete from sysmergesubscriptions, it shows again in Replication monitor.
Post #1662303
Posted Friday, August 7, 2015 1:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 28, 2015 3:26 PM
Points: 47, Visits: 205
Hey Guys so it sounds like solution for creating publication with the same name and deleting it works great for everyone. I just wanted to mention that I too had this issue with Replication Monitor not clearing up errors in 2014. I used sp_removedbreplication and confirmed all replication was gone ( i even dropped databases to make sure) and still got the same error. I realized that the agent jobs for replication for the old publication was still there and re-trying. I ended the job, disabled the jobs and in the next cycle of refresh Replication monitor cleaned up. I the method mentioned above is the cleanest way to do it because if you hand pick internal tables you will not know which ones are clean and left over.

I vote for creating a publication with the same exact name and deleting it to ensure all the internals are cleaned.

Hope this feedback helps.
Post #1709589
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse