SQL Server deletes the distribution database

After the database server deletes the replication (publish subscription), how to delete the database distribution? If you delete the database distribution through the SSMs tool, you will find that there is no delete option at all. Here are the steps to remove distribution

Step 1: View information about the relevant subscriber, and remove the Subscriber name from the registered server if a corresponding subscriber exists

exec sp_helpsubscriberinfo;

Go

exec sp_dropsubscriber ' subscribername ';

Go

Step 2: View the publisher properties of the Distributor

exec sp_helpdistpublisher;

Step 3: Remove the distribution Publisher

exec sp_dropdistpublisher  @publisher = ' PublisherName '

Step 4: Delete the database using SP_DROPDISTRIBUTIONDB distribution

Use master;

Go

exec sp_dropdistributiondb @database = N ' distribution '

Go

Go

Precautions:

1: Error message "Could not drop the distribution database ' distribution '. This Distributor database was associated with a Publisher. "

exec sp_dropdistributiondb @database = N ' distribution '

GO

Msg 14120, Level 16, State 1, procedure sp_dropdistributiondb, line 85th

Could not drop the distribution database ' distribution '. This Distributor database was associated with a Publisher.

This error occurs because the corresponding distribution Publisher must be deleted, or the following error will occur.

2: Error message "Cannot drop the distribution database ' distribution ' because it's currently in use"

exec sp_dropdistributiondb @database = N ' distribution '

Go

Msg 21122, Level 16, State 1, procedure sp_dropdistributiondb, line 124th

Cannot drop the distribution database ' distribution ' because it is currently in use.

The above occurs because the session window uses a database that is distribution, and you can specify the session window's database as master or use the following statement to resolve the issue.

Go

Additionally, for some reason, you cannot use SP_DROPDISTRIBUTIONDB to delete the distribution database. You can force the deletion of the database distribution by using the following method.

Go

drop database distribution;

Or

Use master;

Go

ALTER DATABASE distribution set SINGLE_USER with rollback immediate

Go

drop database distribution;

Go

SQL Server deletes the distribution database