none
How do you drop a publication that is gone from SSMS but shows up in replication monitor? Here's what I've tried... RRS feed

  • Question

  • Hello,

    I had  two test databases on one server that I had been experimenting with for transactional replication.  I wanted to delete the publication from one database and the subscription from the other and finally delete both databases.  I deleted the publication, the subscription , the jobs related to them and finally the databases.  I must confess that i wasn't as careful as I would be if I hadn't planned on just deleting everything.  So what I have now is nothing appearing in SSMS under local publications or local subscriptions for these two test databases (but pubs and subs for other useful things) but in replication monitor the publication shows up with a red x.  

    I cannot use sp_droppublication  @publication='my pub name' because the publication database is gone.  Can I do anything to remove this publication from replication monitor?  This isn't a production server yet it has some users who would be severely inconvenienced if I have to drop all replication and rebuild.  Is there any other way?

    Thanks.


    Monday, April 22, 2013 12:15 AM

Answers

All replies

  • The metadata is likely in MSpublications in the distribution database.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, April 22, 2013 3:35 PM
    Moderator
  • Thanks Hilary!  I deleted the publication from MSpublications but the thing lives as a zombie in replication monitor.  There is progress as now it says "The publication no longer exists" in a grey box at the top of the right pane (in replication monitor) if I select the zombie publication (with a red x) from the list of publication under the server.

    I looked in MSsubscriptions in case there was a leftover subscriber record but there doesn't appear to be as the publication_id is not the same as it was for the publication I just deleted.  Any other system tables in the distribution database I should look at?

    Thanks again!

    Update: just deleted record referring to the old publisher database, which was involved with this publication, from the MSpublisher_databases table but that still didn't help...
    Monday, April 22, 2013 5:34 PM
  • Did you disconnect replication monitor and open it up again?

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, April 22, 2013 6:07 PM
    Moderator
  • Yes, a few times. I also found a record referring to the old publisher database in this table [MSreplication_monitordata] but I wasn't allowed to delete it. Any ideas? Thanks!

    update: upon closer examination, it appears that if I delete the record it appears to work but when I open replication monitor, the record is replaced.  Does this give a clue as to where the zombie record lives such that it's feeding the MSreplication_monitordata table when replication monitor launches?  I looked at the other tables in the distribution database and I haven't been able to find it (or them if it's in more than one table) so far.

    • Edited by ThorNotFromNorway Monday, April 22, 2013 8:11 PM add more "experimentation" results
    Monday, April 22, 2013 6:37 PM
  • The simplest way is to recreate a publication with the same name, and then delete it correctly.

    Monday, April 22, 2013 9:23 PM
  • Thanks but that's the first thing I tried last night. Didn't work in my situation.  I really am hoping to find the final bit that's referring to it and get rid of that.  Thanks though.

    Is there anywhere else besides the MSreplication_monitordata table that I can check to eliminate this zombie publication?  If I delete the record that refers to it from this tableit's simply replaced the next time I launch replication monitor.  Does this provide a clue to where else I can perform surgery to get rid of it?  Thanks.

    Monday, April 22, 2013 9:41 PM
  • The only way I have been able to get rid of that problem is to create a new publication with the same name, and then delete it properly.  You can try this, but it has not worked for me in your situation when it only shows up in Replication Monitor.

    http://www.mssqltips.com/sqlservertip/2710/steps-to-clean-up-orphaned-replication-settings-in-sql-server/

    Tuesday, April 23, 2013 7:35 PM
  • Thanks Tom. I don't have the databases any more.  Should I recreate the publication and subscriber databases, recreate the publication and subscription and then delete properly?   Have you ever had to create the databases as well?  It's not much work but I feel like I tried this when this first happened and it didn't work in my case.  I'm sure it works fine in most cases though.  i will try again tonight and report back.  I have to work on something else today but I can't wait to try your suggestion.
    Wednesday, April 24, 2013 2:42 PM
  • I have never had to recreate the database.  This has always happened to me when a replicated database was restored to another server.  It was impossible to get rid of the replication links unless I recreated the publication and then dropped it on the restored server.

    Thursday, April 25, 2013 9:47 PM
  • Hi, I had a similar case, apply the EXEC sp_removedbreplication, but in the replication monitor even the replication was visualized with a red x, which caused me a false alarm.
    The records MSreplication_monitordata table can not be deleted manually, it is only a guide for the monitor replication, because that's what I understood also try removing your records, but had no success.
    Reaches a point where I thought I had to recreate all replications, but I found a solution.
    Guide me in that table (MSreplication_monitordata), to search for agents publisher that had not been removed (eg DB: SDCGHJ), then search these agents and items in the tables: MSdistribution_agents, MSsnapshot_agents, MSlogreader_agents; located in the distribution database and delete records that made reference to publisher_db SDCGHJ, delete jobs that they made reference to the publication eliminated.
    With these steps the publication is no longer displayed on the monitor replication and "red x" stop appearing.
    I hope it is of your utility.
    • Proposed as answer by tirumala86 Saturday, April 30, 2016 1:37 PM
    Wednesday, March 11, 2015 6:55 PM
  • Thanks for a lot Arrunategui..  I have faced same issue and follow your resolution steps ..issue got fixed ....

    Regards,

    Tirumala

    Saturday, April 30, 2016 1:40 PM
  • Thanks, worked a treat :).
    Thursday, August 1, 2019 5:52 AM