SQL: Cannot drop the database because it is being used for replication

If you go to drop a database, and you’re told you can’t because it’s being used for replication, you might be a little confused. For example, if you check your publications and subscriptions, you might see this:

Given there aren’t any, how could the database be being used for replication? Well, each database has a property that indicates if it’s enabled for replication, even if there aren’t any publications. That’s what’s stopping you from dropping the database.

You can reset that property easily in SQL Server Management Studio (SSMS). Start by right-clicking Replication in Object Explorer, and choosing Publisher Properties:

In the Publisher Properties window, select the Publication Databases page:

Here you can deselect the database that you’re trying to drop.

Once you’ve saved this, you should be able to drop your database.

I hope that helps someone.

Want to learn more?

If you want to let me teach you about replication, try our new online on-demand Working with SQL Server Replication course. You’ll find it, and our other courses, at http://training.sqldownunder.com .

2019-04-04