Quick one today:
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.
- Reset Dist flag
- Force drop distributor
- Manually remove Publishers (from Distributor properties)
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' GO
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 GO
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.
Points to keep in mind: