Post reply

Cannot delete publication from Replication Monitor

  • August 6, 2008 at 6:04 am

    #189042

    I have a database, let's call it Prod_1, and had originally set up transaction replication on it. I called this publication Prod_1_TL. Later it was decided that a once-a-day snapshot would be preferable, so that publication was deleted and I created Prod_1_TL.

    Prod_1_TL never disappeared from Replication Monitor. However, it wasn't a problem until just a couple of days ago when I could not stay connected to the distributor in RM (in this setup, the distributor and the publisher are the same server).

    Browsing around, I found that there is a stored procedure that I could run that would clear that right up, except that sp doesn't exist in SQL 2005, so I ended up deleting Prod_1_TL's entry in the MSpublications table as per another site. Now I am able to stay connected to the distributor, but the old publication is still there and I would like to get rid of it. Help!

  • August 6, 2008 at 8:47 am

    #853876

    try "sp_droppublication": http://msdn.microsoft.com/en-us/library/ms188363.aspx

  • August 6, 2008 at 8:54 am

    #853883

    I did, the response I get is "The publication 'Prod_1_TL' does not exist."

  • August 6, 2008 at 9:33 am

    #853923

    uh ...ok You delete the row in MSpublications...the only thing i suppose is data in the table MSarticles, where are stored the articles for any publications...try to find the articles for the dropped publication

  • August 6, 2008 at 9:41 am

    #853928

    I have deleted everything from that table with the offending publication_id, and I've done the same to all the other tables in distribution that look likely. I really don't know where RM is getting its list of publications.

  • August 6, 2008 at 9:54 am

    #853946

    this seems to work: create a publication with the same name of the oldest-deleted one, and then remove it. take a look: http://www.sqlservercentral.com/Forums/Topic125951-7-1.aspx

  • August 6, 2008 at 10:05 am

    #853961

    Hm, I didn't see that before.

    That did work. This time I dropped the publication using sp_droppublication whereas before I dropped it by right-clicking the publication in SSMS and deleting it. I wonder if that is a bug or it was just a freak occurrence.

  • December 2, 2015 at 5:47 am

    #1843470

    I have done all the above steps but still the publication shows in Replication Monitor.

Viewing 8 posts - 1 through 7 (of 7 total)

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