none
Cannot drop server because it is used as a Subscriber to remote Publisher in replication. RRS feed

  • Question

  • Trying to remove replication from a test environment and have the pubs/subs all removed but when i try to remove the distributor by running sp_dropdistpublisher, I get the error above. I have tried running sp_dropsubscriber which runs fine but still get the same error. Tried running sp_dropdispubliser with the ignore_distributor = 1 no luck same error. Some background, there are 3 sql servers in this environment involved in replication. Server 1 had been the original publisher and distributor, server 2 was a subscriber to the original and server 3 is a new server set up to take over the distribution function. Server 1 and 2 were up and running transactional replication and I removed all replication from them. I set up server 3 as the new distributor and started configuring Server 1 and 2 as publisher and subscriber, had some issues and got put on other tasks and never completed. After a couple weeks I needed the original replication set up of  server 1 as distributor and publisher and server 2 as subscriber for testing a prod change. I dropped the distribution from server 3 (or so I thought) and reconfigured server 1 and 2. That all worked and now, I am back to dropping the replication from server 1 and 2 and configuring server as distributor. When I started on that today I saw there was still a distribution db on server 3 so i tried generating scripts to remove the distributor and that is where I ran into the error. When I look in SSMS under server objects I can still see linked servers to server 1 and 2. Any suggestions on how to get this removed? I would like to do so without taking the distribution db offline and dropping it so it is a clean set up. Thank you
    Thursday, September 28, 2017 7:50 PM

Answers

  • I tried running the individual procs you have in that proc Tom but still got the error  and could not get around the "could not drop server..." error. I think that the set up was just jacked up from the back and forth installs I have been doing. Once I got rid of one of the linked servers I was able to get the rest of it cleaned up. Thanks for your time
    Monday, October 2, 2017 2:05 PM

All replies

  • Try this

    EXEC sp_dropsubscription @pulication='Pulication_Name', @article='all',@subscriber='Server name' on the Server 1

    and

    Exec sp_dropdistpubliser @publisher='Server name.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Friday, September 29, 2017 5:05 AM
  • I had seen this proc but there are no publications so I dont have a pub name to use.
    Friday, September 29, 2017 12:55 PM
  • Try this:

    ALTER PROC [dbo].[usp_Replication_Drop]
    AS
    -- Remove replication from database
    DECLARE @dbname sysname, @srvname sysname;
    SET @dbname = DB_NAME();
    SET @srvname = @@SERVERNAME;
    
    PRINT 'Dropping Replication from ' + @dbname + '.';
    
    BEGIN TRY
        PRINT 'Dropping All Subscriptions';
        BEGIN TRY
    	   -- Drop all subscriptions to database
    	   EXEC sp_dropsubscription @publication='all', @article='all', @subscriber='all';
    	END TRY
    	BEGIN CATCH
    	   -- Sometimes transaction lingers on error
    	   WHILE @@TRANCOUNT>0
    		  ROLLBACK;
    	   -- Try drop with ignore_distributor option
        	   EXEC sp_dropsubscription @publication='all', @article='all', @subscriber='all', @ignore_distributor=1;
    	END CATCH
    
        -- Drop all publications to database
        PRINT 'Dropping All Publications';
        BEGIN TRY
    	   EXEC sp_droppublication @publication='all';
        END TRY
        BEGIN CATCH
    	   -- Sometimes transaction lingers on error
    	   WHILE @@TRANCOUNT>0
    		  ROLLBACK;
    	   -- Try drop with ignore_distributor option
    	   EXEC sp_droppublication @publication='all', @ignore_distributor=1;
        END CATCH
    
        -- Remove replication objects
        BEGIN TRY
    	   EXEC sp_removedbreplication @dbname=@dbname, @type='both';
        END TRY
        BEGIN CATCH
    	   -- Sometimes transaction lingers on error
    	   WHILE @@TRANCOUNT>0
    		  ROLLBACK;
    	   PRINT 'Error Removing replicated objects.'
        END CATCH
    END TRY
    BEGIN CATCH 
        PRINT 'Error: ' + ERROR_MESSAGE();
    END CATCH
    
    -- Sometimes transaction lingers on error
    WHILE @@TRANCOUNT>0
    	   ROLLBACK;
    
    -- Force publish flag to false
    EXEC sp_replicationdboption @dbname = @dbname, @optname = N'publish', @value = N'false';
    
    RETURN;

    Friday, September 29, 2017 6:48 PM
  • Thanks Tom but that is still not getting it. I tried all of the procs with the settings above and I am getting errors because replication is removed from the publication databases (This database is not enabled for publication.)
    Friday, September 29, 2017 7:45 PM
  • What I did to remove/resolve this:

    1) set the distribution db offline

    2) dropped one of the linked servers, the 2 that remained where repl_distributor and the old distributor server 1

    3) ran sp_dropdistpublisher @publisher = N'server 1' , @ignore_distributor = 1

    4) set the distribution db online

    5) ran sp_dropdistributiondb @database = N'distribution'

    6) ran exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

    It appears all has been removed now. 

    Friday, September 29, 2017 8:49 PM
  • Did you try running the proc?  It cleans up all that residual information and should have worked.

    Saturday, September 30, 2017 2:53 PM
  • I tried running the individual procs you have in that proc Tom but still got the error  and could not get around the "could not drop server..." error. I think that the set up was just jacked up from the back and forth installs I have been doing. Once I got rid of one of the linked servers I was able to get the rest of it cleaned up. Thanks for your time
    Monday, October 2, 2017 2:05 PM
  • How did you manage to set the distriibution db offline?  Not an option for me.

    William W. Anderson

    Thursday, November 21, 2019 5:33 PM
  • I don't think there is an option in the gui, I ran something like alter database test set offline with rollback immediate, then run steps above and alter the database back online
    Thursday, November 21, 2019 5:47 PM
  • nevermind, can't do it from ssms menu, had to use tsql

    William W. Anderson

    Thursday, November 21, 2019 5:53 PM