Set database from SINGLE USER mode to MULTI USER

Question

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

User · Answer

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.

User · Answer

Tried everything didn t work Login to that server remotely as we gonna kill all connections  run the below code more than once till it returns completed and no  killing process  test anymore 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

User · Answer

I have solved the problem easily   Right click on database name rename it After changing  right click on database name --  properties --  options --  go to bottom of scrolling RestrictAccess  SINGLE USER to MULTI USER  Now again you can rename database as your old name

User · Answer

This worked fine for me   Take a backup Create new database and restore the backup to it Then Properties   Options    Scroll down  State   RestrictAccess   select Multi user and click OK Delete the old database   Hope this work for all Thank you Ramesh Kumar

User · Answer

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

User · Answer

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

User · Answer

I have just fixed using following steps  It may help you   Step  1      Step  2      Step  3      Step  4      Step  5  Then run following query   ALTER DATABASE YourDBName SET MULTI USER WITH ROLLBACK IMMEDIATE GO   Enjoy

User · Answer

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     Good luck

User · Answer

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  gt  50 AND dbid   DB ID   lt Your DB Name gt    EXEC  kill    GO SET DEADLOCK PRIORITY HIGH ALTER DATABASE   lt Your DB Name gt   SET MULTI USER WITH NO WAIT ALTER DATABASE   lt Your DB Name gt   SET MULTI USER WITH ROLLBACK IMMEDIATE GO   To switch back to Single User mode  you can use   ALTER DATABASE   lt Your DB Name gt   SET SINGLE USER   This should work  Happy coding    Thanks

User · Answer

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

User · Answer

SQL Server 2012   right-click on the DB  gt  Properties  gt  Options  gt   Scroll down  State  gt  RestrictAccess  gt  select Multi user and click OK   Voila

User · Answer

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

User · Answer

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

User · Answer

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  gt  50 -- process spids  lt  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

User · Answer

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

User · Answer

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   lt yourDbName gt     EXEC  kill     Then immediately after  in second query window    USE master ALTER DATABASE  lt yourDbName gt  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

User · Answer

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 --  gt  kill the number in spid field GO  exec sp dboption  dbname    single user    FALSE  GO

User · Answer

just go to database properties  and change SINGLE USER mode to MULTI USER    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

User · Answer

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

User · Answer

You can add the option to rollback your change immediately   ALTER DATABASE BARDABARD SET MULTI USER WITH ROLLBACK IMMEDIATE GO

[sql] Set database from SINGLE USER mode to MULTI USER

Examples related to sql

Examples related to sql-server