Posts Tagged ‘Cannot drop server because it is used as a Subscriber to remote Publisher in replication’

Quick one today:

Edit: Update on April 10th –¬†Added some comments to repeat some steps, as needed

Earlier today, we ran into this error in our lab environment with replication. Two different errors, on the same environment, due to similar sequence of events.

Msg 20584, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 67
Cannot drop server because it is used as a Subscriber to remote Publisher in replication.

Cannot drop server because it is used as a distributor in replication

This type of errors are rare, but one of these three options below might provide some relief.

  1. Reset Dist flag
  2. Force drop distributor
  3. Manually remove Publishers (from Distributor properties)
    1. Invalid object name ‘dbo.MSmerge_agents’

Also, see the notes in the bottom section (points to keep in mind)

Reset Dist Flag for Distributor Instance

Sometimes, the dist flag for Distributor stays set, when not supposed to. So manually resetting it helps.

--  Reset Distributor flag
EXEC master.dbo.sp_ServerOption   @server = 'REPLDist'
				, @OptName = 'dist'
				, @OptValue = 'false'

Force Drop Distributor

One of the last options is to ‘force’ drop distributor. This allows removing distributor database without running through the proper checks. This step is a high risk, high reward option; Since all checks were previously performed and all components of replication are removed, except distributor, this step might provide relief.

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

Manually Remove Publishers

Usually, the above two steps will resolve the issue. But in rare occasions, the issue persists, like in our lab. For those stubborn situation, this technique has given some relief.

Go to Replication >> Distributor Properties >> Go to Publishers (tab)

Now, un-check each Publisher and save. Important to note that, this needs to be done for one publisher at a time.


3.1 Invalid object name MSmerge_agents

In Step 3, sometimes we might run into an error like this:

Invalid object name 'dbo.MSmerge_agents'.

Not sure what the root cause is, but this seems to help:

  1. Create two tables in Distributor database
  2. Rerun Step 3

1. Create two tables in Distributor database

NOTE: This step is not necessary in all situation. So, proceed with caution.

Looks like these two tables are missing in Distribution database. Create them in the Distribution database. And then go back to Step 3 again. Or Step 3 & 2, as needed.

Note: There is no harm in adding these two tables because, after you create these tables and then successfully drop replication (including the distributor database) the new objects will be removed along with the Distributor database.

So you have a clear system at the end.

create table msmerge_agents(
	  id						INT
	, name						NVARCHAR(100)
	, publisher_id				SMALLINT
	, publisher_db				SYSNAME
	, publication				SYSNAME
	, subscriber_id				SMALLINT
	, subscriber_db				SYSNAME
	, local_job					BIT
	, job_id					BINARY(16)
	, profile_id				INT
	, anonymous_subid			UNIQUEIDENTIFIER
	, subscriber_name			SYSNAME
	, creation_date				DATETIME
	, offload_enabled			BIT
	, offload_server			SYSNAME
	, sid						VARBINARY(85)
	, subscriber_security_mode	SMALLINT
	, subscriber_password		NVARCHAR(524)
	, publisher_login			SYSNAME
	, publisher_password		NVARCHAR(524)
	, job_step_uid				UNIQUEIDENTIFIER

CREATE table MSmerge_subscriptions(
	  publisher_id		SMALLINT
	, publisher_db		SYSNAME
	, publication_id	INT
	, subscriber_id		SMALLINT
	, subscriber_db		SYSNAME
	, subscription_type INT
	, sync_type			TINYINT
	, status			TINYINT
	, subscription_time DATETIME

Points to keep in mind:

Sometimes, just the one of these steps might be enough to resolve the issue; Sometimes, it might take two of the steps. In our lab, we ended up needing all the 3 steps. the first step is easy to implement.
The second and third steps might need repeated attempts. Something like:
  1. Try Step 2, if it fails,
    1. then try step 3;
    2. If it fails, try Step 3.1
    3. If that succeeds, then go back to Step 2.
  2. Once Step 2 is successful, the distributor is fully cleared and cleaned.
Hope this helps,

Read Full Post »