Dropping Offline Databases? Be Careful!

calendar Oct 17, 2018 · SQL Server  ·
Share on: linkedin copy

Well, maybe the title is a little bit harsh. You absolutely can drop a SQL Server database that's offline, but remember that its files won't be deleted if you do.

Safely Dropping Databases

At a previous employer, we had a well-defined process when dropping databases for a client. It went something like this:

  1. Confirm in writing the databases on which servers/instances to be dropped
  2. Take a final full backup of databases
  3. Take databases offline
  4. Wait at least two weeks to make sure nothing breaks in the absence of this database
  5. Drop databases

This is a pretty good and safe method. If taking the database offline causes some unforeseen system to stop working, it can be very quickly brought back online in-place, instead of having to locate the backup and restore it. But it there's just one problem…

A Classic "Gotcha"

If a database is offline when it gets dropped, its files don't get deleted. This is because databases that are offline have been closed, and SQL Server is no longer accessing the files for that database. In fact, a common method of moving database files involves setting the database offline, because you can then move or copy the files to their new location. Probably the only downside to SQL Server no longer accessing an offline database's files is that when the database is dropped, they won't be deleted. This is well-documented:

If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer.

Solutions

The way I see it, there are two ways to address this unfortunate side-effect:

  • Delete all the files manually after dropping the offline database
  • Bring the database back online before dropping

I very much prefer the latter method, and when done properly the database is online for a very short period of time before it is finally dropped. Either way though, be mindful that dropping an offline database means you have some additional cleanup work to do!