For various reasons, you may need to manually remove SQL Server replication. Microsoft has a good KB article which shows how to do it. It has always worked in the past, but I ran into a situation a couple of weeks ago where it couldn’t remove the distributor via sp_dropdistributor. I don’t recall what error we were getting, but I didn’t have time to figure out what was wrong so with the help of a Microsoft engineer we were able to setup another distributor and then get replication working again. Later I tried “drop database” command on the original distribution database, but it said it couldn’t be dropped since it was being used for replication. It no longer was being used for replication, so it seemed to be orphaned.
Yesterday, I opened a case with Microsoft to see how I could get rid of the orphaned distribution database. It’s a simple fix:
alter database distribution set offline;
drop database distribution;