set database to multi_user through management studio

Question

I have had to set my database to single_user mode to allow for a dbcc checkdb repair and now I am unable to get my database back to multi_user. I have tried the following command in a query window against the master database but it hasn't worked as suggested by another Stack overflow post:

USE [master]; 
GO 

ALTER DATABASE mydb SET MULTI_USER WITH ROLLBACK IMMEDIATE; 
GO

I get the following error:

Msg 5064, Level 16, State 1, Line 2 Changes to the state or options of database 'mydb' 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 2 ALTER DATABASE statement failed.

If I right click on the database and try selecting properties then it errors saying that it is already in use.

Any help would be greatly appreciated.

If you have any job, or SQL-Agent started, stop all them (except MSSQLSERVER) and -with sa user- try again. — shA.t, Mar 14 '15 at 22:09

Dan Guzman · Answer 1 · 2022-06-06T10:40:15.857

7

Try killing the existing connection and setting MULTI_USER in the same batch:

USE master; 
GO
DECLARE @sql nvarchar(MAX);
SELECT @sql = STRING_AGG(N'KILL ' + CAST(session_id as nvarchar(5)), ';')
    FROM sys.dm_exec_sessions
    WHERE database_id = DB_ID(N'mydb');
SET @sql = @sql + N';ALTER DATABASE mydb SET MULTI_USER;';
--PRINT @sql;
EXEC sp_executesql @sql;
GO

edited Jun 06 '22 at 10:40

answered Mar 14 '15 at 23:52

Dan Guzman

43,250
3
46
71

This Query is AWESOME to change from single user to MULTI USER Database – kucluk Mar 01 '19 at 14:40
Mamma Mia, this finally saved me!! Thanks @Dan! – DurkoMatko Apr 22 '20 at 09:03

score 3 · Accepted Answer · answered Mar 15 '15 at 01:42

To get the session that was tied to the database I ran:

SELECT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('mydb')

This yielded a processid of 55

I then ran:

kill 55 go

And I was the able to use the alter multi_user line that was previously not working

score 0 · Answer 3 · answered Mar 21 '19 at 13:16

The most likely reason why you're getting this error is that you still have the original session open that you used to set the database to single user. If you execute the above SQL in the same session that you set the database to single user in, it will work.

Cengiz Poyraz · Answer 4 · 2015-03-14T22:09:27.843

First try selecting the master database and run the alter command.

If it does not work, There exists some open connections to database and you can check and kill the processes

use master
GO

select 
d.name, 
d.dbid, 
spid, 
login_time, 
nt_domain, 
nt_username, 
loginame
from sysprocesses p 
inner join sysdatabases d 
    on p.dbid = d.dbid
where d.name = 'dbname'
GO

kill 52 -- kill the number in spid field
GO

exec sp_dboption 'dbname', 'single user', 'FALSE'
GO

set database to multi_user through management studio

4 Answers4