Guy Ellis Rocks

C# ASP.NET SQL SERVER

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.

Filed under: SQL Server

» Similar Posts

  1. CREATE FILE encountered operating system error 5 Access is denied
  2. LINQ to SQL architecture question
  3. Copy data from local table to remote database table

» Trackbacks & Pingbacks

    No trackbacks yet.
Trackback link for this post:
http://guyellisrocks.com/trackback.ashx?id=40

» Comments

  1. guy ellis avatar

    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

    guy ellis — June 27, 2008 12:21 AM
  2. Mark del Campo avatar

    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

    Mark del Campo — October 24, 2008 4:34 PM
  3. Ilija Brajkovic avatar

    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.

    Ilija Brajkovic — June 15, 2009 8:47 PM
  4. Shah avatar

    Same situation as Mark and guy ellis

    Thanks

    Shah — July 7, 2009 5:56 AM
  5. Johnny avatar

    Same situation but re-starting SSMS doesn't help.

    SET SINGLE user doesn't work also it says ALTER DATABASE command failed

    Johnny — October 20, 2009 10:19 AM
  6. guy ellis avatar

    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.

    guy ellis — October 20, 2009 10:23 AM

» Leave a Comment