Post reply

delete of an offline database

  • August 17, 2012 at 2:40 pm

    #279855

    I'm wondering why if I delete an obsolete database that is currently active via SSMS that it also removes the data and log files but if the database is offline before the delete it does not remove the data and log files?

    Thanks for any explanation of why the behavior is different between the two states.

  • August 17, 2012 at 2:42 pm

    #1527252

    Strange. What version number of SQL Server is it?

  • August 17, 2012 at 2:48 pm

    #1527254

    Documented behaviour.

    From Books Online - Drop Database page

    Dropping a Database

    Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. 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. To remove a database from the current server without deleting the files from the file system, use sp_detach_db.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 17, 2012 at 2:50 pm

    #1527257

    I found this out on SQL Server 2008 R2 and confirmed on 2005 too.

  • August 17, 2012 at 2:50 pm

    #1527258

    Funny I never noticed before. Thanks Gail.

  • August 17, 2012 at 2:53 pm

    #1527260

    chumphrey 12211 (8/17/2012)

    I'm wondering why if I delete an obsolete database that is currently active via SSMS that it also removes the data and log files but if the database is offline before the delete it does not remove the data and log files?

    Thanks for any explanation of why the behavior is different between the two states.

    I can't say why it is done that way, but that is the documented behavior of the DROP DATABASE command

    From SQL Server 2008 Book Online:

    http://msdn.microsoft.com/en-us/library/ms178613(v=sql.100).aspx

    "DROP DATABASE (Transact-SQL)

    ...

    Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted..."

  • August 17, 2012 at 2:55 pm

    #1527261

    Thanks Gail, I was looking under delete a database and it said nothing about it.

  • August 17, 2012 at 3:57 pm

    #1527274

    I suspect it's because when the DB is offline, SQL does not have a lock on the files, hence they could easily be open in another app (hex editor, antivirus, file copy, even another instance of SQL) and hence it is not guaranteed that it can delete the files. With an online DB SQL has the files locked exclusively and hence knows for sure that it can delete those files.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply