Post reply

Cleaning up Replication Monitor

  • April 6, 2010 at 9:25 am

    #1146558

    Nice !!

    Adding a new Publication with the name and the deleting it worked perfectly.

    Many Thanks

  • August 6, 2010 at 7:40 am

    #1203515

    Bravo...I'm gratified with your solution. It worked...:-)

  • January 6, 2011 at 10:40 am

    #1270202

    what about if you just remove the publication from the table select * from MSpublications , would that work too?

  • April 22, 2011 at 2:45 am

    #1314809

    I got the same problem on a SQL Server 2008, a general database failure (the files got deleted and the database was put offline by sql server at the same time)occured yesterday in 1 of our non-critical databases.

    It was quickly restored but the replication was unable to work again, after spending a few hours looking I found out a new publication had been created on the restored database and that this was conflicting with the publication from for the crash.

    This was strange since our company policy in such cases says that the old publication has to be removed and recreated. We found out that we could recreate the publication under the old name but their were still problems. Deleting it kept it in the replication monitor, this morning I once again recreated the publication under the same name and deleted it and the publication was removed in replication monitor.

  • October 13, 2011 at 6:02 am

    #1393955

    Old thread but the trick of creating a new subscription to the same target box and then cleaning it up using exec sp_dropmergesubscription worked for me too.

    Replication monitor is not clean and not reporting any errors.

    Oh, and I am under 2008R2

  • July 20, 2012 at 7:08 am

    #1515502

    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.

  • November 23, 2012 at 1:09 pm

    #1561928

    nuwins solution is Perfect, worked perfectly! 😀

  • June 12, 2013 at 7:44 pm

    #1623546

    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 = **

  • June 20, 2013 at 10:15 pm

    #1626190

    I just clean up the very old publication's from the replication monitor using this tables. Thanks!

  • August 4, 2014 at 8:06 am

    #1734856

    you should delete a row in table [distribution].[dbo].[MSsnapshot_agents], the database distribution is in system's database.

  • January 26, 2015 at 10:49 pm

    #1773294

    adding a new publication with same name and removing it works . Finally i could delete all those red marked publications from the replication monitor !! 😀

  • February 21, 2015 at 3:43 am

    #1779111

    So how it would be deleted from the Replication monitor? though every time i delete from sysmergesubscriptions, it shows again in Replication monitor.

  • August 7, 2015 at 1:35 pm

    #1818271

    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.

  • January 9, 2017 at 4:27 pm

    #1921509

    What cleanup table in MSDB?

  • August 29, 2017 at 8:07 am

    #1957241

    2005_DBA - Wednesday, June 12, 2013 7:44 PM

    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 = **

    type your message

        Do you also need delete records from this table MSsubscriptions?

Viewing 15 posts - 16 through 30 (of 31 total)

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