Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

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;

Print | posted on Wednesday, September 02, 2009 1:59 PM |

Feedback

Gravatar

# 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.
9/22/2009 1:10 PM | Sunil Bonagiri
Gravatar

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

testing
9/24/2009 10:55 AM | Eddy
Gravatar

# 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.
11/20/2009 1:06 AM | Binod
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET