I broke the Distribution database

I've been dragged away from the creative side of my job to fix creaking infrastructure recently. It's not a part of the job I enjoy. But I recognise I've reached a level at which I may never again be able to escape back into pure development.

This weeks fun involved a corrupt LDF files on a production server. The server is virtual and appears to be having disk issues. The databases in question continued to mirror and replicate without issue and a test restore followed by DBCC checks revealed no database corruption or data loss.

To fix the corrupt ldf files we had to failover to our mirror, break the mirroring for the affected database, restore form a full backup and re-sync with the mirror again. It worked fine. But the second corruption coming just 6 days after the first suggested an underlying disk issue so our Network guys suggested we nuke the log file  disk partition and recreate it. We failed-over all the mirrored databases and stopped replication from the suspect server. We also moved the ldf files for a few small less important databases away from the dodgy partition.

The disk work done, we had a few hours of reconfiguration to get the environment back to normal. However, when I attempted to setup replication again I realised I'd made a stupid mistake. The distribution database - which is used extensively by SQL Server in replication - was broken as I'd not moved the ldf file for this database. Googling the fix didn't turn up much of use. Most posts were DBA's having issues with replication after "accidentely" dropping the distribution database. None of the posts help me solve the particular issue I'd created. Consequently it took a couple of hours to find a solution. Hence why I've blogged it here.

The problem:
My Distribution database has no ldf file anymore. I want to drop the broken distribution database but decommissioning using the wizard initiated by right clicking Replication in the SSMS UI returns errors.

The solution:
MSDN revealed a number of system stored procedures for managing the distribution database and the procedure shown below with the addition of the parameter @Ignore_Distributor = 1 has the effect of removing the distribution database cleanly with 'No Questions Asked'. This enabled me to then use the simple UI wizard for reconfiguring a new distribution database and quickly establishing replication again.

 USE [master]  
 EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1  
 GO  

What we learned this week.

• Creative DBA work is more fun than high stress open heart surgery on your production database.
• Our DR solution works!!
• Our documentation is non-existent and despite my best efforts will probably remain that way.