[SOLVED] How do I go back to multi

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

Solution

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.