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.
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.
ReplyDeleteRunning this on Server 2003
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.
ReplyDeleteThanks for the post
One way of resolving this problem is to put
ReplyDeleteUSE 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.
Same situation as Mark and guy ellis
ReplyDeleteThanks
Same situation but re-starting SSMS doesn't help.
ReplyDeleteSET SINGLE user doesn't work also it says ALTER DATABASE command failed
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.
ReplyDeletethank you so much. m$ is horrible.
ReplyDeletegood it helped me
ReplyDeleteThanks a lot Ilija Brajkovic :)
ReplyDeleteMany thanks, Ilija Brajkovic....
ReplyDeleteMany thanks Ilija Brajkovic — it worketh
ReplyDeleteThanks Ilija Brajkovic!! you saved my day. best wishes to you!
ReplyDeletewith C# (.NET) :
ReplyDeleteServer server = new Server(".");
server.Databases[dbName].SetOffline();
server.Databases[dbName].Drop();
with SSMS: check box 'Close existing connections' when you are deleting database
Thanks, @Ilija Brajkovic - it worked.
ReplyDeleteThanks to Ilija Brajkovic :)
ReplyDeleteThanks a lot Ilija Brajkovic :)
ReplyDeleteit worked
Wow,,, good day.
ReplyDeleteThanks a lot Ilija Brajkovic :)
it worked...