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:
ALTER DATABASE distribution SET OFFLINE
DROP DATABASE distribution
— then follow the steps to reconfigure replication.
Hope this helps,