SQL Server Error: Cannot drop database because it is being used for replication.

Hey everyone! Welcome (or also hopefully "Welcome back") to SQL Something. :-)

This week’s instalment is going to be a little short and focused on fixing the above error that I ran into. 

The general consensus is that this error occurs after Replication was removed from a database and then you attempt to delete the DB. Apparently there might be some replication metadata that was left behind that would cause SQL Server to believe that replication is still taking place. If you attempt to delete the DB, you’ll get the error and it may go into "single user mode" (Access to the DB is restricted to one user; I may make a post on how to get out of it in the future).

As usual I’m gonna post everything I read on how to fix it as well as what I actually did.

Preliminary checks

• The first step is to double check that you have actually removed replication (the subscriptions and the publications) from the DB.
• Secondly you can further double check what DBs are actually marked for replication through the use of sp_helpreplicationdboption. This will list all the DBs on your instance and will indicate whether they are configured to publish through the use of a '1':

Fig 1. TestRep is a publisher as indicated by the '1' in the transpublish column.

Small note: sp_helpreplicationdboption is supposed to be a replacement for an old stored procedure called sp_helreplicationdb which did not list all DBs, just the ones that are configured to publish. I have used them both however and they both seem to work.

Fix – Option A

The first fix is really more of a workaround. 

• Make an empty DB with the same name as your problem DB on a separate SQL instance.
• Back up the newly created empty DB.
• Right click the problem DB, go to Tasks - > Restore -> Database.
• In the restore database screen, choose the blank DB backup that you just made and go to the Options tab and choose "Overwrite…" as shown below and click Ok.
• Delete the problem DB.

Fig 2. Ensure that you tick the Overwrite option.

The DB would now be overwritten with the blank one and you should be able to delete it. Honestly, I’m not too fond of this approach so now we’ll look at Option B.

Fix – Option B

This fix removes ALL replication objects (minus any data already replicated; you’ll need to delete that DB manually). Also it’s pretty simple. It uses the sp_removedbreplication stored procedure.

• Run the following query: sp_removedbreplication 'DBName' where 'DBName' is the name of your DB.
• Delete DB.

See? Simple enough. If there are any downsides (haven’t seen/read about any) please feel free to say in the comments section below.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)