Saturday, June 14, 2008

Cannot drop database because it is currently in use

I was getting this error when trying to drop a database:
Cannot drop database "MyDatabaseName" because it is currently in use.

I tried the sp_who command to see if there was anything holding on to the DB that I hadn't disconnected from. Couldn't see anything.

drop database MyDatabaseName kept on failing.

Eventually I closed SQL Server Management Studio (SSMS) and reopened it and the drop command then worked immediately. No idea why this happened but this was the solution.

17 comments:

  1. Happened again and same solution. This time I was trying to restore a DB. So closed SSMS and reopened it and the restore ran straight away. I believe that SSMS had been left open for several days with about 4 query windows open that were being used on an ad hoc basis.
    Running this on Server 2003

    ReplyDelete
  2. I had the same issue on SQL Server 2005. I couldn't find any open connections to the database. I had left SSMS open for a few days. I closed SSMS and was able to drop the database.
    Thanks for the post

    ReplyDelete
  3. One way of resolving this problem is to put
    USE master
    GO
    before deleting your database. If you still have this problem, try to put
    ALTER DATABASE MyDatabaseName
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE
    before deleting your database.

    ReplyDelete
  4. Same situation as Mark and guy ellis
    Thanks

    ReplyDelete
  5. Same situation but re-starting SSMS doesn't help.
    SET SINGLE user doesn't work also it says ALTER DATABASE command failed

    ReplyDelete
  6. Sorry that didn't work for you Johnny. Have you tried rebooting the machine? I know that's the lamest of all suggestions but it does seem to produce the best types of results in situations like this.

    ReplyDelete
  7. thank you so much. m$ is horrible.

    ReplyDelete
  8. Thanks a lot Ilija Brajkovic :)

    ReplyDelete
  9. Many thanks, Ilija Brajkovic....

    ReplyDelete
  10. Many thanks Ilija Brajkovic — it worketh

    ReplyDelete
  11. Thanks Ilija Brajkovic!! you saved my day. best wishes to you!

    ReplyDelete
  12. with C# (.NET) :
    Server server = new Server(".");
    server.Databases[dbName].SetOffline();
    server.Databases[dbName].Drop();
    with SSMS: check box 'Close existing connections' when you are deleting database

    ReplyDelete
  13. Thanks, @Ilija Brajkovic - it worked.

    ReplyDelete
  14. Francky VercruysseMay 25, 2012 at 5:54 PM

    Thanks to Ilija Brajkovic :)

    ReplyDelete
  15. Francky VercruysseMay 25, 2012 at 5:55 PM

    Thanks a lot Ilija Brajkovic :)
    it worked

    ReplyDelete
  16. Wow,,, good day.
    Thanks a lot Ilija Brajkovic :)
    it worked...

    ReplyDelete