How to Drop Database which is in Single User Mode in SQL Server

You are a developer or DBA for SQL Server, and you have set the database to Single User Mode. You want to delete the database right now. Nevertheless, using the statement below to drop results in one of two errors.

Msg 924, Level 14, State 1, Line 27
Database '' is already open and can only have one user at a time.

Msg 3702, Level 16, State 4, Line 9
Cannot drop database "" because it is currently in use.

The next step:

EXEC sp_who2

You received a list of all processes, but the database name is not among them. a procedure that is not active. You attempted to drop the database once more but were met with the same error.

Now that you've performed the query below to obtain more details, you're hoping it would return the process id so you can terminate it and subsequently drop the database.

Select * from sys.sysprocesses

where DB_Name(dbid)='YourDBName'

You receive no results from this search. That is disappointing. It won't allow us drop the database, and when we try to find it, it isn't there.

Instead of using sp_who2 and sys.sysprocesses, use below query and kill the spid.

SELECT request_session_id

FROM   sys.dm_tran_locks

WHERE  resource_database_id = DB_ID('YourDBName')

Once above query return session id, use below to kill it. Let's say if above query return you 56. You will run Kill 56.

Kill 56

You can execute the drop database statement after killing the Process id. It should drop the Single User Mode database without any issues.

drop database [YourDatabaseName]