How to Drop Database in Single_User Mode

How do I drop a database which shows DatabaseName (Single User) as its name?

When I try to delete it, I get the following error:


Alter failed for Database 'DatabaseName'. (Microsoft.SqlServer.Smo)


ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5064)

I tried to execute the ALTER below and still have the same issue.

ALTER DATABASE [DatabaseName] SET MULTI_USER WITH NO_WAIT

If you are going to drop a database, you have to be the only connection to that database. If any other connections are there, you can't drop it. From the error message (that error means your database is in Single_User mode but there is already a connection so you can't connect) my assumption here is that you tried to set it to Single_User mode and then attempted to do the drop but either you grabbed a connection that you didn't know about, or some other process has. The fact that restarting SSMS worked for you tells me it was probably you grabbing that connection. So here is how you can fix that.

Logically you have to put the database back into multi_user mode so you can then put it into single_user mode again (but this time you'll be in control of that single connection allowed and drop the database before something else connects) and then your database will be gone.

In code here is how you need to do this (but first close your query windows that are connected to that database. Restart SSMS and make sure you don't select this database in the object browser):

-- Then attempt to take your database to multi_user mode, do this from master
USE MASTER 
GO

ALTER DATABASE myDatabaseName 
SET multi_user WITH ROLLBACK IMMEDIATE
GO

-- Now put it into single_user mode and drop it. Use Rollback Immediate to disconnect any sessions and rollback their transactions. Safe since you are about to drop the DB.
ALTER DATABASE myDatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE myDatabaseName
GO

StackExchange Database Administrators Q#30349, answer score: 28