dmigo dmigo - 18 days ago 5
SQL Question

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

Answer

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.