SQL Server – Distribution Database Recovery Pending Issue

replicationsql server

I was trying to create a distribution database and I succeded at first, but since I checked the box to configure SQL server Agent to start automatically, the last step of configuring the distribution failed, but the distribution database was created.

However, I haven't realized that, and in order to repeat the process from scratch, I deleted the data files and log files of the distribution database even before dropping the database, and I didn't realize that distribution database will be created in system databases. After deleting the mdf and ldf files I see recovery pending besides that database. Even after I tried to drop the dristribution database using sp_dropdistributor I still see the icon of that database with recovery pending. I just want to get rid of that database. What should I do?

Best Answer

Can you try below (substitute with appropriate names - publisherdb, publisher, distribution database) :

-- Disable the publication database.
USE [AdventureWorks2012]
EXEC sp_removedbreplication @publicationDB;

**-- Remove the registration of the local Publisher at the Distributor.**
USE master
EXEC sp_dropdistpublisher @publisher;

-- Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;

-- Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO
Related Question