Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cleaning up Replication Monitor


Cleaning up Replication Monitor

Author
Message
simon.coleman
simon.coleman
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 48
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.
pnorlin
pnorlin
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 212
nuwins solution is Perfect, worked perfectly! :-D
2005_DBA
2005_DBA
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 483
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 = **
amarnath.sahadevan
amarnath.sahadevan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 108
I just clean up the very old publication's from the replication monitor using this tables. Thanks!
cesarsosa.p
cesarsosa.p
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
you should delete a row in table [distribution].[dbo].[MSsnapshot_agents], the database distribution is in system's database.
ravi.deadcancer
ravi.deadcancer
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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 !! :-D
emuqatash
emuqatash
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 24
So how it would be deleted from the Replication monitor? though every time i delete from sysmergesubscriptions, it shows again in Replication monitor.
Sponge-592981
Sponge-592981
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 223
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.
felixms
felixms
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 2
What cleanup table in MSDB?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search