Set database from SINGLE USER mode to MULTI USER

I need help with setting a database that was restored in `SINGLE_USER` mode to `MULTI_USER`. Every time I run

ALTER DATABASE BARDABARD
SET MULTI_USER;
GO

I get this error:

>Changes to the state or options of database 'BARDABARD' cannot be made at this time.
>
>The database is in single-user mode, and a user is currently connected to it.

It needs to be in non-`SINGLE_USER` mode to set it to another mode, but I can’t set the database in any another mode while it is `SINGLE_USER` mode.

SQL Server 2012:

right-click on the `DB > Properties > Options > [Scroll down] State > RestrictAccess > select Multi_user` and click OK.

Voila!

You can add the option to rollback your change immediately.

ALTER DATABASE BARDABARD
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
GO

The code below has worked for me when I didn't know the specific SPID that was used to change into `singleuser` mode.

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

1. Tried everything didn't work
2. Login to that server remotely as we gonna kill all connections
3. run the below code more than once till it returns completed and no "killing process" test anymore
4. reactivate it again using the code below the below code

> use master GO declare @sql as varchar(20), @spid as int
>
> select @spid = min(spid) from master..sysprocesses where dbid =
> db_id('DB_NAME') and spid != @@spid
>
> while (@spid is not null) begin
> print 'Killing process ' + cast(@spid as varchar) + ' ...'
> set @sql = 'kill ' + cast(@spid as varchar)
> exec (@sql)
>
> select
> @spid = min(spid)
> from
> master..sysprocesses
> where
> dbid = db_id('DB_NAME')
> and spid != @@spid end

then to bring it back alive

> ALTER DATABASE DB_NAME SET MULTI_USER; GO

This worked fine for me

1. Take a backup
2. Create new database and restore the backup to it
3. Then Properties > Options > [Scroll down] State > RestrictAccess > select Multi_user and click OK
4. Delete the old database

Hope this work for all
Thank you
Ramesh Kumar

I have solved the problem easily

1. Right click on database name rename it

2. After changing, right click on database name --> properties --> options --> go to bottom of scrolling RestrictAccess (SINGLE_USER to MULTI_USER)

3. Now again you can rename database as your old name.

I actually had an issue where my db was pretty much locked by the processes and a race condition with them, by the time I got one command executed refreshed and they had it locked again... I had to run the following commands back to back in SSMS and got me offline and from there I did my restore and came back online just fine, the two queries where:

First ran:

USE master
GO

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('<yourDbName>')

EXEC(@kill);

Then immediately after (in second query window):

USE master ALTER DATABASE <yourDbName> SET OFFLINE WITH ROLLBACK IMMEDIATE

Did what I needed and then brought it back online. Thanks to all who wrote these pieces out for me to combine and solve my problem.

This worked fine for me.

Step 1. Right click on database engine, click on activity monitor and see which process is having connection. Kill that particular user and execute the query Immediately.

Step 2.

USE [master];
GO
ALTER DATABASE [YourDatabaseNameHere] SET MULTI_USER WITH NO_WAIT;
GO
and refresh the database.

The “user is currently connected to it” might be SQL Server Management Studio window itself. Try selecting the master database and running the `ALTER` query again.