Tag: and a user is currently connected to it.

Error: 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.

One of my colleague had an issue in dropping a database in her testing environment. She was not able to drop a database as she gets an error message (as below). Let me try to provide what she tried and ended up for every ones understanding.
alter database [dbname] set multi_user with rollback immediate
Drop database [dbname]

Error Message:

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.

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]

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!