Set database from SINGLE USER mode to MULTI USER

On more than 3 occasions working with SQL Server 2014, I have had a database convert to Single User mode without me changing anything. It must have occurred during database creation somehow. All of the methods above never worked as I always received an error that the database was in single user mode and could not be connected to.

The only thing I got to work was restarting the SQL Server Windows Service. That allowed me to connect to the database and make the necessary changes or to delete the database and start over.

It may be best to log onto the server directly instead of using SQL Management Studio

Ensure that the account you login as is dbowner for the database you want to set to MULTI_USER. Login as sa (using SQL server authentication) if you can

**If your database is used by IIS, stop the website and the app pool that use it** - this may be the process that's connected and blocking you from setting to MULTI_USER

USE MASTER
GO

-- see if any process are using *your* database specifically

SELECT * from master.sys.sysprocesses
WHERE spid > 50 -- process spids < 50 are reserved by SQL - we're not interested in these
AND dbid=DB_ID ('YourDbNameHere')

-- if so, kill the process:

KILL n -- where 'n' is the 'spid' of the connected process as identified using query above

-- setting database to read only isn't generally necessary, but may help:

ALTER DATABASE YourDbNameHere
SET READ_ONLY;
GO

-- should work now:

ALTER DATABASE Appswiz SET MULTI_USER WITH ROLLBACK IMMEDIATE

Refer here if you still have trouble:

[To see links please register here]

**AS A LAST ALTERNATIVE** - If you've tried everything above and you're getting desperate you could try stopping the SQL server instance and start it again

If the above doesn't work, find the loginname of the spid and disable it in Security - Logins

**just go to database properties**
and change SINGLE USER mode to MULTI USER

[![enter image description here][1]][1]


[1]:

NOTE:
if its not work for you then take Db backup and restore again and do above method again

*
Single=SINGLE_USER

Multiple=MULTI_USER

Restricted=RESTRICTED_USER

After going in to the Single-User mode, a client can establish only ONE connection with the SQL Server, remember that "Object Explorer" takes up a (separate) connection, so if you are trying run a Multi-User statement in a query window, you will get error that in Single-User mode you can't establish another connection.

For me this wasn't the issue though, in my case, there were few automated processes that were persistently (in every few seconds) establishing connections, so as soon as I took the DB into Single-User mode and disconnected myself, one of the processes established/occupied the connection (before I could start my Restore operation). As soon as I'd kill those connections - they'd reconnect and when I ran the Restore command, I would get the error that connection is already occupied.

To resolve this I had to write the `kill` statements, change of `User-Mode` statements and `Restore` operations all in one query window and when I ran them all in one go, voila!!! it worked.

Hope this helps others.

I was having trouble with a local DB.

I was able to solve this problem by stopping SQL server, and then starting SQL server, and then using the SSMS UI to change the DB properties to Multi_User.

The DB went into "Single User" Mode when i was attempting to restore a backup. I hadn't created a backup of the target database before attempting to restore (SQL 2017). this will get you every time.

Stop SQL Server, Start SQL Server, then run the above Scripts or use the UI.

I have just fixed using following steps, It may help you.

**Step: 1**

[![right click on single user database][1]][1]

---
**Step: 2**

[![take offline][2]][2]

---
**Step: 3**

[![Drop connection and take offline][3]][3]

---
**Step: 4**

[![Take online][4]][4]

---
**Step: 5**

Then run following query.

ALTER DATABASE YourDBName
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
GO

Enjoy...!

[1]:

[2]:

[3]:

[4]:

I googled for the solution for a while and finally came up with the below solution,

SSMS in general uses several connections to the database behind the scenes.

**You will need to kill these connections before changing the access mode.**(I have done it with **EXEC(@kill);** in the code template below.)

Then,

Run the following SQL to **set the database in MULTI_USER mode.**

USE master
GO
DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses
WHERE spid > 50 AND dbid = DB_ID('<Your_DB_Name>')
EXEC(@kill);

GO
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO


To switch back to Single User mode, you can use:

ALTER DATABASE [<Your_DB_Name>] SET SINGLE_USER

This should work. Happy coding!!

Thanks!!

I had the same issue and it fixed by the following steps - reference:

[To see links please register here]

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 56 --=> kill the number in spid field
GO

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

You couldn't do that becouse database in single mode. Above all right but you should know that: when you opened the sql management studio it doesn't know any user on the database but when you click the database it consider you the single user and your command not working.
Just do that: Close management studio and re open it. new query window without selecting any database write the command script.

USE [master];
GO
ALTER DATABASE [tuncayoto] SET MULTI_USER WITH NO_WAIT;
GO



make f5 wolla everything ok !