alter database [dbname] set multi_user with rollback immediate Drop database [dbname]
Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database ‘dbname’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.Solution: When I analysed, I could find that the database has gone into single user mode and there was an open session on this database.Since, its a testing environment, I had killed the open session from the database and tried to drop the database by putting it multi user as first step as below and it was successful.
Hope this helps if you come across similar situations. I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
USE master; DECLARE @killSessions varchar(8000) = ''; SELECT @killSessions = @killSessions + 'kill ' + CONVERT(varchar(5), spid) + ';' FROM master..sysprocesses WHERE dbid = db_id('dbname') EXEC(@killSessions); alter database [dbname] set multi_user with rollback immediate --Drop database [dbname] /*Only if need to be dropped*/