SQL SERVER – Error: Deleting Offline Database and Creating the Same Name

Offline database is very interesting subject, and there are a couple of interesting details associated with it, which one must know.

There are two common queries related to offline database:

1)      My hard drive is getting full and I deleted my ‘offline’ databases. After deleting my offline databases, my hard drive is still full and there is no empty space.

2)      I recently deleted the ‘offline’ database, and now, when I am attempting to create database with the same name, it is giving me error that the database file already exists.

I can see why these questions are coming up frequently. The common understanding that seems to prevail is that when any database is deleted, files associated with the database (.mdf, .ndf and .ldf) are deleted automatically. This is true for any database that is an ‘online’ database. However, the behavior is bit different in the case of the ‘offline’ database. When any ‘offline’ database is deleted, files associated with the database are not deleted automatically. All the files related to the ‘offline’ database need to be deleted manually after deleting the offline database.

How to Clean Up After You Drop an Offline Database

The easiest way to avoid this trouble is to note the file paths before you drop anything. sys.master_files still lists the files of a database while it is offline, so run SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('YourDB'); and save the result.

You then have two clean options:

• If the files are healthy, bring the database online first with ALTER DATABASE YourDB SET ONLINE, then drop it. SQL Server removes the files for you.
• Drop it while it is offline, and then delete the .mdf, .ndf and .ldf files yourself from the paths you saved.

If you already dropped it and lost track of the files, look in the default data and log folders of the instance, and compare the file names with the database name. Before you delete any file, make sure no other database uses it by checking sys.master_files again. The error about the file already existing goes away as soon as the old files are removed or you pick new file names. If disk space allows, move the old files to another folder for a few days before you delete them, in case someone still needs that data.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.