none
Database 'TEST' is already open and can only have one user at a time. RRS feed

  • Question

  • Hi all,

    Could some help on this issue as per urgency!

    

    Database 'TEST' is already open and can only have one user at a time.

    I also tried this command but having the same error, please let me know how to troubleshoot this issue

    Use Master

    GO

    Select * from master.sys.sysprocesses

    Where spid > 50

                And dbid=DB_ID (‘StuckDB’))  -- replace with your database name

    Thanks

    • Moved by Kalman Toth Tuesday, May 12, 2015 12:06 AM Not database design
    Thursday, April 30, 2015 2:27 PM

Answers

  • Ok.

    Check if there is any database maintenance or backups going on, if there is then stop these.

    Another thing you could try is to create a SQL Server Agent Job, owner is sa, database is Master, schedule to run at startup, to run the following command: -



    ALTER DATABASE SUSDB
    SET MULTI_USER
    WITH ROLLBACK IMMEDIATE

    And then restart SQL Server.


    Please click "Mark As Answer" if my post helped. Tony C.


    • Edited by Anthony C-UK Thursday, April 30, 2015 2:47 PM
    • Marked as answer by karjdba Wednesday, July 22, 2015 12:47 PM
    Thursday, April 30, 2015 2:46 PM

All replies

  • Sysprocesses does not have the database name in it's structure, try this instead.

    select * from sysprocesses where dbid IN

    (select dbid from sysdatabases where dbname='StuckDB')


    Please click "Mark As Answer" if my post helped. Tony C.


    Thursday, April 30, 2015 2:33 PM
  • Your database is set to Single user only

    Try below query to change it to multi user

    USE [master]
    
    ALTER DATABASE TEST
    SET MULTI_USER
    WITH ROLLBACK IMMEDIATE
    Hope this will help


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET forum


    Thursday, April 30, 2015 2:34 PM
  • Hi Tony,

    sorry got the same error again, it can't allow me sp_who2 as well

    I tried above command got the following error

    Changes to the state or options of database ‘TEST’ 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, 

    Thursday, April 30, 2015 2:40 PM
  • That is because you might be running it from same database connection, use master database to run your query.

    Also, you can run below query to get the session 

    use [master]
    
    SELECT request_session_id
    FROM   sys.dm_tran_locks
    WHERE  resource_database_id = DB_ID('TEST') 

    and then kill the session

    exec kill <Your connection session id>
    I have also provided you the answer to switch to multi user mode [if you want to do it]


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET forum


    Thursday, April 30, 2015 2:44 PM
  • Ok.

    Check if there is any database maintenance or backups going on, if there is then stop these.

    Another thing you could try is to create a SQL Server Agent Job, owner is sa, database is Master, schedule to run at startup, to run the following command: -



    ALTER DATABASE SUSDB
    SET MULTI_USER
    WITH ROLLBACK IMMEDIATE

    And then restart SQL Server.


    Please click "Mark As Answer" if my post helped. Tony C.


    • Edited by Anthony C-UK Thursday, April 30, 2015 2:47 PM
    • Marked as answer by karjdba Wednesday, July 22, 2015 12:47 PM
    Thursday, April 30, 2015 2:46 PM
  • Your database is set to Single user only

    Try below query to change it to multi user

    USE [master]
    
    ALTER DATABASE TEST
    SET MULTI_USER
    WITH ROLLBACK IMMEDIATE
    Hope this will help


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET forum


    He will only be able to do this if he has exclusive access to the Database first...


    Please click "Mark As Answer" if my post helped. Tony C.

    • Proposed as answer by Kalman Toth Tuesday, May 12, 2015 12:06 AM
    Thursday, April 30, 2015 3:07 PM
  • @Tony : agreed, but this is one of the solution which you have also mentioned. 

    OP didn't mentioned about permission on the database hence this solution might work for him :)


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET forum

    Thursday, April 30, 2015 3:19 PM
  • Yep it's working fine ..Thanks 
    Wednesday, July 22, 2015 12:47 PM
  • This has improved my quality of life by 400%.  Thank you.
    Tuesday, April 2, 2019 8:08 PM
  • This worked for me in 2016.
    Ran and killed a few times as the connection seemed to jump about session IDs after each kill. no need to restart SQL or restore database :) thank you 
    Monday, June 24, 2019 3:21 PM