locked
How do i delete a publication RRS feed

  • Question

  • Hi ,

    i was trying to set up transactional replication between 2 servers( Publisher A, Distributor/Subscriber B). The distributor was configured on a different remote machine. After the creation of publication on Server A we ran into a problem with blocking, So i had to disable the Distributor and i tried to do the same with the publisher. the distributor was successfully disabled, but the publication continues to appear in the SSMS under Publications. 

    Also i ran Sp_dboption on the published Database and got the following results 

     

    published

    torn page detection

    auto create statistics

    auto update statistics.

     

    I did try Dropping the publication by using sp_droppublication but it did not drop the publication. I need to set up the replication again so i need to delete the old publication. how can i delete/drop the publication without blocking. Also does dropping the publication affect the published database. Please advise. 

     

    Thanks,

    Leo

    • Edited by LEOROY Wednesday, November 30, 2011 9:54 PM
    Wednesday, November 30, 2011 9:52 PM

Answers

  • dropping the pub should not have an effect on the use db.

    as per removing the pub try sp_removedbreplication, helps in most cases and if it does not then see if yoy still have objects in the published db such as sysarticles, syssubscriptions and syspublication and verify if there is any data in the distribution database at MSPublications etc

    http://msdn.microsoft.com/en-us/library/ms188734.aspx


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    • Proposed as answer by Yaniv Etrogi Thursday, December 1, 2011 8:48 PM
    • Marked as answer by LEOROY Monday, December 5, 2011 5:19 PM
    Wednesday, November 30, 2011 10:43 PM

All replies

  • dropping the pub should not have an effect on the use db.

    as per removing the pub try sp_removedbreplication, helps in most cases and if it does not then see if yoy still have objects in the published db such as sysarticles, syssubscriptions and syspublication and verify if there is any data in the distribution database at MSPublications etc

    http://msdn.microsoft.com/en-us/library/ms188734.aspx


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    • Proposed as answer by Yaniv Etrogi Thursday, December 1, 2011 8:48 PM
    • Marked as answer by LEOROY Monday, December 5, 2011 5:19 PM
    Wednesday, November 30, 2011 10:43 PM
  • Thursday, December 1, 2011 4:09 AM
  • Here is the reliable T-SQL sequence for removing a publication:

    USE [databaseName]                                    --very important to be in correct database context

    GO

    sp_removedbreplication 'databaseName'    --this removes the publication system tables from the database

    GO

    sp_droppublication 'publicationName'         --or use sp_dropmergepublication if merge publication

    GO

    -----------------------------------------------------------

    I have only tested this with merge publications, but it should work the same with transactional replication


    Dan Randolph
    • Proposed as answer by Dan Randolph Monday, March 26, 2012 3:12 AM
    Sunday, December 4, 2011 7:13 PM