Change DB(AxDB) from Single user mode to Multi user mode

 Change DB(AxDB) from Single user mode to Multi user mode

Sometimes during Dev instance DB refresh, AxDB changed to Single user.

To change DB from single mode to multi mode, sometime we need to Kill N number of PId's and sometimes that's time taking process.

Following SQL help to change DB from Single user to Multi user in one go:

USE MASTER

GO

DECLARE @DatabaseName AS VARCHAR(128)

DECLARE Cur CURSOR FOR

--List of Database for Multi User Mode

 SELECT name from

sys.databases

where user_access_desc='Single_USER'

OPEN Cur

FETCH Next FROM Cur INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

  BEGIN

  

--Kill all user connection in case open for any database

  DECLARE @Spid INT

DECLARE KillProcessCur CURSOR FOR

 SELECT request_session_id

FROM   sys.dm_tran_locks

WHERE  resource_database_id = DB_ID(@DatabaseName) 

OPEN KillProcessCur

FETCH Next FROM KillProcessCur INTO @Spid

WHILE @@FETCH_STATUS = 0

  BEGIN

      DECLARE @SQL VARCHAR(500)=NULL

      SET @SQL='Kill ' + CAST(@Spid AS VARCHAR(5))

      EXEC (@SQL)

      PRINT 'ProcessID =' + CAST(@Spid AS VARCHAR(5))

            + ' killed successfull'

      FETCH Next FROM KillProcessCur INTO @Spid

  END

CLOSE KillProcessCur

DEALLOCATE KillProcessCur

--Inner Cursor Ends

--Outer Cursor: Put DB in Multi User Mode

      DECLARE @SQLSingleUSer NVARCHAR(MAX)=NULL

      SET @SQLSingleUSer='ALTER DATABASE ['+@DatabaseName+'] 

      SET MULTI_USER WITH ROLLBACK IMMEDIATE'

         Print @SQLSingleUSer

         EXEC (@SQLSingleUSer)

      FETCH Next FROM Cur INTO @DatabaseName

  END

CLOSE Cur

DEALLOCATE Cur

--Query to check DB's are in Multi user Mode ?

Select name as DBName,state_desc,user_access_desc 

from sys.databases 

where user_access_desc='MULTI_USER'

and database_id>4