CODEWITHSUNDEEP
sqlsql-server
Rahna1970418
Rahna1970418

Reputation: 2435

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.

Upvotes: 237

Views: 893217

Answers (21)

sgeddes
sgeddes

Reputation: 62831

That error message generally means there are other processes connected to the DB. Try running this to see which are connected:

exec sp_who

That will return you the process and then you should be able to run:

kill [XXX]

Where [xxx] is the spid of the process you're trying to kill.

Then you can run your above statement.

Upvotes: 101

Alvimar
Alvimar

Reputation: 498

What I do:

First I kill all database process, to certificate that database isn't in use. After I alter the user mode.

Sometimes a process is quickly grabed again from an aplication, and I need run this process one more time...

Replace the expression DATABASE_NAME below, to use your database name.

--Kill process

USE Master 
GO

declare @sql as varchar(20), @spid as int

select @spid = min(spid)  from master..sysprocesses  where dbid = db_id('DATABASE_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 
        spid > 50 AND dbid = db_id('DATABASE_NAME') 
        and spid != @@spid 
end 

print 'Killing Process completed...' 
GO

**--Alter user mode** 

ALTER DATABASE DATABASE_NAME SET MULTI_USER; 

GO 

print 'Alter Database Process completed...'

Upvotes: 1

Hamit YILDIRIM
Hamit YILDIRIM

Reputation: 4539

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 !

Upvotes: 5

Azadeh Khojandi
Azadeh Khojandi

Reputation: 4074

I had the same issue and it fixed by the following steps - reference: http://giladka8.blogspot.com.au/2011/11/database-is-in-single-user-mode-and.html

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

Upvotes: 25

Trilok Pathak
Trilok Pathak

Reputation: 3101

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!!

Upvotes: 0

Rikin Patel
Rikin Patel

Reputation: 9383

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

Step: 1

right click on single user database

Step: 2

take offline

Step: 3

Drop connection and take offline

Step: 4

Take online

Step: 5

Then run following query.

ALTER DATABASE YourDBName
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
GO

Enjoy...!

Upvotes: 24

hanzolo
hanzolo

Reputation: 1150

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.

Upvotes: 0

Eddie Kumar
Eddie Kumar

Reputation: 1480

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.

Upvotes: 0

Hassan Saeed
Hassan Saeed

Reputation: 7080

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

enter image description here

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

Upvotes: 0

Aileen Gregg
Aileen Gregg

Reputation: 23

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

Upvotes: 1

Chris Halcrow
Chris Halcrow

Reputation: 31940

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:

http://www.sqlservercentral.com/blogs/pearlknows/2014/04/07/help-i-m-stuck-in-single-user-mode-and-can-t-get-out/

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

Upvotes: 7

epak96
epak96

Reputation: 401

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.

Upvotes: 0

paul
paul

Reputation: 22001

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.

Upvotes: 140

sunil guragol
sunil guragol

Reputation: 201

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.

Upvotes: 18

Shane G.
Shane G.

Reputation: 101

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.

Upvotes: 10

Vamsi
Vamsi

Reputation: 9

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.

Upvotes: 0

Ramesh Kumar
Ramesh Kumar

Reputation: 19

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

Upvotes: 1

Bishoy Hanna
Bishoy Hanna

Reputation: 4589

  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

Upvotes: 1

Jaypal Reddy
Jaypal Reddy

Reputation: 21

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

Upvotes: 2

LG1
LG1

Reputation: 921

You can add the option to rollback your change immediately.

ALTER DATABASE BARDABARD
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
GO

Upvotes: 90

Div2504
Div2504

Reputation: 455

SQL Server 2012:

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

Voila!

Upvotes: 43

Related Questions