Unable to login more than 1 user and found that database is in Single User mode

Method 1:

1. Connect your database with SSMS (SQL Server Management Studio)

https://support.qne.com.my/a/solutions/articles/81000385373 

2. First, we need to kill all active connection/processes of the database
3.  
     
4. Right-click your server instance in Object Explorer, and then select New Query

4. Paste the following T-SQL code snippet into the query window and replace ‘SampleDBReplicate’ to your database code:

-----Replace SampleDBReplicate with your database name-----

USE master

GO

DECLARE @kill varchar(max) = '';

SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '

FROM master..sysprocesses 

WHERE spid > 50 AND dbid = DB_ID('SampleDBReplicate')

EXEC(@kill);

5. Execute query and all active connection and processes of the database has been terminated
6. Now proceed to set the database access from Single User to Multi-User
     
7. Right-click on the ‘Single User Mode’ database and select Properties

8. Go to Options and scroll to bottom
9. Under category ‘State’, change ‘Restrict Access’ from ‘SINGLE_USER’ to ‘MULTI_USER’ and click okay

Method 2:

1. Connect your database with SSMS (SQL Server Management Studio)

https://support.qne.com.my/a/solutions/articles/81000385373 

2. Right-click your server instance in Object Explorer, and then select New Query

3. Paste the following T-SQL code snippet into the query window and replace ‘SampleDBReplicate’ to your database code:

-----Replace SampleDBReplicate with your database name-----

USE master

GO

DECLARE @kill varchar(max) = '';

SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '

FROM master..sysprocesses 

WHERE spid > 50 AND dbid = DB_ID('SampleDBReplicate')

EXEC(@kill);

GO

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [SampleDBReplicate] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [SampleDBReplicate] SET MULTI_USER WITH ROLLBACK IMMEDIATE

GO

4. Execute query and it will kill all active connection/ processes and set database access from single User to Multi-User

** Learn How to rebuild and reorganize indexes

** Learn more about Azure Cloud

** Learn more about QNE Hybrid Cloud Software