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?

1 Answer 1


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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.