Tara Kizer Blog

Tara Kizer

How to remove a SQL Server distribution database if other methods fail

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:

use master
go
alter database distribution set offline;
drop database distribution;

Legacy Comments


Sunil Bonagiri
2009-09-22
re: How to remove a SQL Server distribution database if other methods fail
Great..!! It worked... I had same issue but nothing else worked. Thank you.

Eddy
2009-09-24
re: How to remove a SQL Server distribution database if other methods fail
testing

Binod
2009-11-20
re: How to remove a SQL Server distribution database if other methods fail
Great..!! It worked... I had same issue but nothing else worked. Thank a lot.