I can, pretty accurately, quote you what I said prior to coming up with this post: “Why won’t you delete? You stupid #*$&, just delete!” That said, I suspect this is a rare issue, and I should certainly hope one that is never encountered in production. The reason I say this is because I put myself into this little pickle because I was re-aligning datafiles with some new LUNs to de-clutter a developer SQL Server and I neglected to remove the distributor role from the server before I dropped the LUNs and … YUP! You guessed it! Bubye database files for the distribution DB! This led to a long and arduous process of trying to get rid of this thing after the fact. It was fairly difficult to find documentation on doing this, but here is a more accurate description of the issue:
- Distribution DB in “Recovery Pending”
- Path to data files does not exist any more
- Distributor and Publisher roles still active
In this scenario, you can’t remove the distributor and publisher roles because the distribution DB is in an inaccessible state. Prior to this, I did attempt to put the database in emergency mode, which did not work – I suspect this being due to the database being marked as read_only in this state. I also attempted to drop the database in emergency mode as well as disable distribution and publishing. I also ran into issues with the sp_dropdistributor stored procedure not working (with the no_checks and ignore_distributor flags set). Here is the process that I ultimately found to work:
alter database distribution set offline;
drop database distribution;
The reason this was so difficult to find was the undocumented gem at the end (sp_removesrvreplication). If you ever find yourself in this unlucky predicament, I hope this helps!