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]
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] /*Only if need to be dropped*/
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!
Like this:
Like Loading...
Related
SQLZealots 