How do I go back to multi_user mode

Question

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

score 1 · Answer 1 · answered Jan 12 '15 at 15:13

1

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

answered Jan 12 '15 at 15:13

HaveNoDisplayName

8,291
106
37
47

It could be the case sometimes, but what if I want to do that from my program? Is there any other way to achieve that? – dmigo Jan 12 '15 at 15:18
my question is when SET SINGLE USER fails and throw exception, so database is already in multi-user state, why are you setting that – HaveNoDisplayName Jan 12 '15 at 15:24
This is a deinstallation scenario. I want to set `[MyDatabase]` to `single_user`, then to drop db. And I want to set it back to `multi_user` in case of exception during the drop. – dmigo Jan 12 '15 at 15:25
thats my point, first DB is multi-user, then you try to set as Single USer, but fails, so DB is automatically reamins in multi-user state? isn't – HaveNoDisplayName Jan 12 '15 at 15:26
Not really. It is successfully set to single-user, then I try to drop db, fail to do so, db stays in single-user mode. – dmigo Jan 12 '15 at 15:28

score 1 · Answer 2 · edited Nov 19 '16 at 09:15

1

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?

edited Nov 19 '16 at 09:15

marc_s

732,580
175
1,330
1,459

answered Jan 12 '15 at 15:39

Antonio

535
10
26

Is it guaranteed that db will be taken by my user? If it is guaranteed, then this variant suits me completely. – dmigo Jan 12 '15 at 15:45
1

I thought that it is forbidden to have `ALTER DATABASE` inside of transaction in ms sql. – dmigo Jan 12 '15 at 15:45
1

If you can set it to multi-user, then the currently logged user should be the only user in the DB. – Antonio Jan 12 '15 at 15:49

score 1 · Answer 3 · answered Jan 12 '15 at 16:12

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

score 1 · Accepted Answer · answered Jan 12 '15 at 19:49

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.

score 1 · Answer 5 · answered Apr 18 '22 at 20:21

Find out the user and the session id

write a sql query as: sp_who (this will show all the processes)

Note down the “spid” of the user from the above query result and kill that session.
Change your db to multi-user mode

enter the below query

ALTER DATABASE [your db]

SET MULTI_USER

How do I go back to multi_user mode

5 Answers5