Cleaning up Replication Monitor

  • I have sucessfully removed an old publication and it subscriptions, but I am still seeing references to it in the Replication Monitor under Publishers, Snapshot Agents, and Distribution Agents. When I try to delete the listing under Publishers I get an error message that states the following:

    Error 21776:[SQL-DMO] The name 'Publication1' was not found in the TransPublications collection. If the name is a qualified name, use{} to separate various parts of the name and try again.

    When I try to look at the properties of the agents I get the following error message:

    Error 14262: The specified @job_id('xxxyyyzzz') does not exist.

    So it appears that the old publication and its components are in fact gone (or at least to SQL Server they cannot be found), but these error messages are still there and I cannot remove them. While this is not affecting any functionality, it "looks" like something is wrong when viewed through SQL-EM.

    I am using SQL_EM to manage these replication services, but can run procs through Query Analyzer if that will help.

    Any help or direction would be greatly appreciated.

    -Chris

  • You may have to refersh the JOBS window to reflect the changes.


    Kindest Regards,

  • I'v encountered the same problems with a replication, When the publication was removed it didnt get totally deleted from the publication server. It all happend some time ago so I don't quite know anymore how I solved it, but I do remember that it made a difference how I removed the publication: by unchecking the database in published databases via the tools menu, or by removing the publication in the Replication tree from Enterprise manager

    I think it is a glitch in SQL ...


    Bas Schouten

    CBIS BV

  • Turns out I was able to clear everything out by disabling replication (luckily it was on the test server ) and then re-enabling it.

    I also needed to run sp_MSLoad_replication_status to clear out the error marks in replication monitor. I have also heard that rebooting achieves the same result, bu this was not an option for me.

    -Chris

  • I've come across this problem several times and I'm still not sure what causes it. Previously the only solution I had was to disable replication and re-create all the subscriptions (by scripting the replication before deleting it of course).

    However the last time this happened I created a new subscription with the same name as the subscription that was causing the problem and SQL Server let me do it! If you then go on to delete the subscription that replication shows is in error and refresh replication, both the new subscription and the old one have gone and replication shows no errors!

  • What nuwins suggested worked!

    I had a nasty red X in replication monitor for a publication that I had long ago deleted.  So I just made a quick publication with the same name (it allowed me to do this!), and then the red X went away.  Then I deleted the publication normally and the problem was solved!

  • Brilliant, that worked exactly perfectly for me, too. Thanks!

  • Another solution for this will be cleanup table in MSDB.

    MohammedU
    Microsoft SQL Server MVP

  • This worked great nuwins. I didn't have to remove all publications, just create one with the EXACT same name as the one your are trying to delete. Then delete the publication and it will be gone from the replication monitor. You're kind of "tricking" SQL. Thanks for the help.

  • What if your subscription is on a machine that is no longer available?

  • I would still try what Nuwins suggests. If you're having the same problem as the others on here, we didn't have an issue with the subscription, but rather the publication was still showing up when it is actually not there. This is really just a way to trick SQL. I believe once you delete a server with subscriptions on it, it contacts the distribution server and deletes those subscriptions; however, the publications can still exist. I could be wrong though.

  • This same thing has happened to me and what resolved it in my case was to open up the MSreplication_agent_status table in tempdb and delete a record that had a publication named the same as the one I had been trying to get rid of in the first place. No more red Xs!

  • I also had to remove remenents of the subscription from the distribution database. Tables: MSsubscriptions and MSpublications

  • Take a look at distribution.dbo.MSReplication_Monitordata as well. It may indicate that you have a logreader agent still set up. I had to remove it from the MSlogreader_agents table in order for my monitor to stop reporting a replication issue when there wasn't any.

    EDIT: SQL2005

  • Nice solution, added a merge publication with the same name and was able to clear replication monitor. Thanks again!

Viewing 15 posts - 1 through 15 (of 31 total)

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