Cleaning up Replication Monitor

  • Chris Wasser

    SSC-Addicted

    Points: 400

    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

  • TriggerMe

    SSChampion

    Points: 11752

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


    Kindest Regards,

  • Bas Schouten

    SSC Veteran

    Points: 278

    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

  • Chris Wasser

    SSC-Addicted

    Points: 400

    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

  • nuwins

    SSC Enthusiast

    Points: 157

    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!

  • David Poluxt

    Old Hand

    Points: 355

    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!

  • Darin-394772

    SSC Journeyman

    Points: 77

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

  • MohammedU

    SSCoach

    Points: 19644

    Another solution for this will be cleanup table in MSDB.

    MohammedU
    Microsoft SQL Server MVP

  • Wired-528862

    SSC-Addicted

    Points: 450

    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.

  • Adrian Robertson

    Mr or Mrs. 500

    Points: 531

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

  • Wired-528862

    SSC-Addicted

    Points: 450

    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.

  • annejo

    SSC Eights!

    Points: 970

    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!

  • madyson3

    SSC Enthusiast

    Points: 159

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

  • ceeatch

    SSC Rookie

    Points: 37

    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

  • wmleonar

    Valued Member

    Points: 59

    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