DROP distribution database: The last option

There are many situations why one would want to do this.This is my situation:

I have replication set up on a Sql Server 2005 going to a failover server. But for some reason replication failed and I did not know about it until today. Yeah, I know, I forgot to add Replication alerts to notify me on any unwanted replication events. So, now the replication is now working. So I deleted the subscription on the FailOver server. But when I try to delete publication on the main server, it says “Could not connect to Distribution because of blah blah blah……. (some reason that is not relevant after one month)” Had I known about this when it happened, I could have troubleshooted it, but now its point of no return.

I cannot connect to distribution database as it was not allowing any connection requests. And I could not to try the steps indicated in Microsoft KB article . So now, I need the Last Option technique before I could reconfigure replication.

So the last option is:
USE MASTER
GO

ALTER DATABASE distribution SET OFFLINE
GO

DROP DATABASE distribution
GO

— then follow the steps to reconfigure replication.

Hope this helps,

_SqlTimes

Read Full Post »