none
Unable to bring database back into MULTI_USER mode.

    Question

  • I'm wondering if somebody could provide insight into a problem I'm having with SQL Server 2005.  Although the problem is happening wthin an SSIS ETL, I don't think this problem is SSIS related.

     

    In the ETL I need to rename a database, so I first put the database into single-user mode by issuing the command:

     

    ALTER DATABASE foobar SET SINGLE_USER WITH ROLLBACK 30

     

    The database then goes into single-user mode, and after the renaming occurs, I attempt to put the same database back into multi-user mode:

     

    ALTER DATABASE foobar SET MULTI_USER WITH ROLLBACK IMMEDIATE

     

    However, whenever I have a query pane opened against the same database in SQL Server Management Studio, the ETL fails and I get this error message:

     

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

     

    I'm wondering why the ALTER DATABASE command does not kill off the active connections?  This is on my development box, and I'm the only one connected to the database.  I've tried with ROLLBACK 30 as well, same thing.  If I cut and paste the same command into Mangement Studio, the command succeeds so I don't think its a permission issue (using Windows Authentication both Management Studio and the ETL are executed by the same login).  If I close the query pane the ETL succeeds at restoring multi-user mode.  Is there something I am missing?  Thanks in advance!

    Monday, September 24, 2007 12:20 AM

Answers

  • Run SP_Who and see who is connected to the database when the database is in single user mode. Because , to set it back  to Multi user mode it need exclusive lock on the DB which is already in single user mode and used by some other process. General mistake is the SSMO its self may be connected to that DB. Kill the Process and then set it back to MultiUser

     

    Madhu

    Monday, September 24, 2007 8:10 AM

All replies

  • Run SP_Who and see who is connected to the database when the database is in single user mode. Because , to set it back  to Multi user mode it need exclusive lock on the DB which is already in single user mode and used by some other process. General mistake is the SSMO its self may be connected to that DB. Kill the Process and then set it back to MultiUser

     

    Madhu

    Monday, September 24, 2007 8:10 AM
  •  

    Thanks for the reply Madhu. 

     

    Using sp_who shows a single process connected to the database - the cmd name is "SELECT" the state is "RUNNABLE" and the login is my own.  I did execute a SELECT * FROM ... command in the SSMS query window, however, this command takes only a second to execute, and finished hours before I issued the ALTER DATABASE SET MULTI_USER command from the ETL.  Now I'm wondering:

     

    1) Does the termination clause on the SET MULTI_USER command have no effect?

    2) Does a query in SSMS keep a connection open as long as the query window is active?

     

    Thanks again!

    Monday, September 24, 2007 1:25 PM
  • Yes it keeps the connectin open as long as you have an open QA window. If you see in the drop down for database in this window you can see the database selected. Change the database in this combo to any other database the n the connection to the database will close. then you can set the database back to multi user mode.

     

     Madhu

     

    Monday, September 24, 2007 4:14 PM
  • Thanks again.  I've done some more testing outside of SSMS, and I think my problem is possibily a bug in SQL Server 2005 (e.g. ALTER DATABASE command ignores the termination clause when used with MULTI_USER).  In my test environment, I have created 2 stand alone C# programs:

     

    1) Opens a connection, and leaves it open

    2) Changes the user mode of the database to either or SINGLE_USER or MULTI_USER based on user input.  The ALTER DATABASE command uses the termination clause WITH ROLLBACK IMMEDIATE.

     

    Test 1

     

    1) Put the DB into MULTI_USER mode

    2) Open a connection

    3) Put the DB into SINGLE_USER mode

     

    In this case, the open connection is killed, so the termination clause is working.  I've verified this by using sp_who inbetween steps 1 & 2 and 2 & 3 (in a query window not connected to the DB used in the test).

     

    Test 2

     

    1) Put the DB into SINGLE_USER mode

    2) Open a connection

    3) Put the DB into MULTI_USER mode

     

    In this case, the open connection remains alive and the ALTER DATABASE .. SET MULTI_USER command fails with an error about the user not being able to log in.

     

    Test 3

     

    1) Put the DB into SINGLE_USER mode

    2) Put the DB into MULTI_USER mode

     

    Finally, this testcase works.  There is a similar bug opened with Microsoft already, although not identical:

     

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=228961

     

    Any thoughts or workarounds would be very much appreciated.

     

    Cheers,

    Monday, September 24, 2007 6:00 PM
  • Try to catch victim using DMV below..

    select * from sys.dm_tran_locks where resource_database_id= 16

    When you see the result find which user hold lock on database.

    kill that particular user using kill user_id

    Run command:

    alter database db_name

    set multi-user

    Saturday, September 22, 2012 7:37 PM
  •  Simple  work around which i did was ...

    Just detach the data base while detaching select the check box drop all action connections .

    then attach it again.

    hope it will help you.


    Ali Maqbool | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

    Tuesday, January 29, 2013 11:04 AM
  • What works for me is:

    1. sp_who to get the SP_ID that holds the connection; and
    2. Issue a KILL <SP_ID>; then finally
    3. sp_dboption <database name>, 'single user', false;

    In some cases processes as say the Windows Service quickly grabbing the connection to the "single user" mode db so there will be little chance reverting changes. You need to be very quick.


    Arthur My Blog

    Wednesday, October 30, 2013 5:49 PM