CODEWITHSUNDEEP
sql-serverdatabasemulti-usersingle-user
Jay
Jay

Reputation: 3082

set database to multi_user through management studio

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.

Upvotes: 4

Views: 13481

Answers (4)

Dan Guzman
Dan Guzman

Reputation: 46193

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

Upvotes: 7

Giles Roberts
Giles Roberts

Reputation: 6883

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.

Upvotes: 0

Jay
Jay

Reputation: 3082

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

Upvotes: 3

Cengiz Poyraz
Cengiz Poyraz

Reputation: 11

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

Upvotes: -2

Related Questions