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.

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

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Database, SQL Server Management Studio
Previous Post
SQL SERVER – Plenty of SQL Community Updates
Next Post
SQL SERVER – Bad Practice of Using Keywords as an Object Name – Avoid Using Keywords as an Object

Related Posts

8 Comments. Leave new

  • Interesting fact..just wanted to why it is designed like that?

    Reply
  • Nice explanation… and definitely its a new knowledge for me..

    Thanks & Regards
    Nikhildas

    Reply
  • Once my manger asked me to delete offline database’s to free the space…..but target was not achieved…..now i understand why i wasn’t successful…Thanks…

    Reply
  • Nilesh Molankar
    December 8, 2011 2:15 pm

    Nice .. simple article.. Thanks Pinal Dave.

    Reply
  • That problem happen to me a lot when the database is restoring and I drop the database because a restore failure.

    Reply
  • I have faced the same problem. Now after reading your post i know the reason.

    Thanks Sir

    Reply
  • Can you please explain a bit further as to what are those files. I have made a sample program where I am attempting to create a new database using C#. Routine involves checking if file exists, and deleting the file if that is true, thereafter it attempts to create the database again. Interestingly the routine succeeds on its first attempt but fails when I try using the same database again, even though the database files have been deleted.

    Reply
  • Interesting Topic…Thanks pinal

    Reply

Leave a Reply