When removing replication using the wizard, you may encounter the following error message:
Msg 21122, Level 16, State 1, Procedure sp_dropdistributiondb …
Cannot drop the distribution database ‘distribution’ because it is currently in use.
Why this Happens
The reason for the above message is because the agents are still running on the server. Disable the agents and then run the Disable publisher and distribution wizard to get rid of the distribution database.
You can also run sp_who2 to verify which process is using the distribution database and then terminate it and run the wizard, although this is not generally recommended.
How to Fix It
Stop all replication agents and SQL Server Agent Jobs
Disable or stop jobs like Snapshot Agent, Log Reader Agent, Distribution Cleanup Agent, etc.
Identify and terminate active sessions manually
Run the below query to identify the sessions that are running.
SELECT spid
FROM sys.syasprocesses
WHERE dbid = DB_ID('distribution');Close or kill any remaining active sessions, then rerun the disable wizard or sp_dropdistributiondb .
Please Consider Subscribing
