CODEWITHSUNDEEP
sqlsql-servert-sqlmulti-usersingle-user
dmigo
dmigo

Reputation: 3029

How do I go back to multi_user mode

Here is my query:

IF EXISTS(%some query%)
BEGIN
    BEGIN TRY       

        ALTER DATABASE [MyDatabase] SET single_user WITH ROLLBACK IMMEDIATE;

        --do something
        --throw some exception

    END TRY
    BEGIN CATCH

        --here I want to set database back to multi_user

        RAISERROR ('DBErrorMessage', @ErrorSeverity, @ErrorState);
    END CATCH
END

What is the best way to set database back to multi_user? I am afraid that the most straightforward way might lead to an

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

exception.

By the most straightforward way I mean this one: 

ALTER DATABASE [MyDatabase] SET MULTI_USER

Upvotes: 0

Views: 9086

Answers (5)

coder kemp
coder kemp

Reputation: 478

  1. Find out the user and the session id
  • write a sql query as: sp_who (this will show all the processes)

  1. Note down the “spid” of the user from the above query result and kill that session.

  2. Change your db to multi-user mode

  • enter the below query

    ALTER DATABASE [your db]

    SET MULTI_USER

Upvotes: 1

Antonio
Antonio

Reputation: 535

What happens if you simply put

ALTER DATABASE [MyDatabase] SET MULTI_USER

after the CATCH ? It shouldn't find any active user since you are in single user from your program/stored procedure?

Also, have you tried putting everything in a transaction, using ROLLBACK in the CATCH block?

Upvotes: 1

Alex Weitzer
Alex Weitzer

Reputation: 181

It's been a while, but I believe the with rollback immediate option is there to say you want this to succeed no matter what. the normal behavior blocks until all running transactions have completed successfully.

Also, setting the database to multi-user mode when it's already multi-user is safe.

However, if you want to be really sure, use a nested try catch block. The first one to handle any error moving to single user mode, and the inner one to handle errors that occur in single-user mode..

see try-catch documentation and alter database documentation

A couple of final notes for completeness:

1) If the error is severe enough your connection will be closed, and your database will still be in single-user mode.

2) You should have a test instance where you can safely try this stuff out, so that you can learn what's going to happen in production.

Upvotes: 1

Antonio
Antonio

Reputation: 535

I just made a quick test:

BEGIN TRY       

    ALTER DATABASE DB1 SET SINGLE_USER
    SELECT 1/0 --it generates an error

END TRY
BEGIN CATCH
   ALTER DATABASE DB1 SET MULTI_USER 
END CATCH

It appears to be working, after the batch completes DB1 is still in Multi-User mode...

Upvotes: 1

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8487

The "current user is currently connected to it" might be SQL SERVER MANAGEMENT STUDIO window itself.

SELECT master database, and run

ALTER DATABASE TherapyDatabaseWTS
SET MULTI_USER;
GO

Or if you want to do with SQL SERVER MANAGEMENT STUDIO, then follow

Right click Database >> Properties >> Options >> State >> RestrictAccess 
                                                  >> Choose Multi_user and click OK

Upvotes: 1

Related Questions