Scenario
Unable to login more than 1 user and found that database is in Single User mode
Solution
Method 1:
- Connect your database with SSMS (SQL Server Management Studio)
https://support.qne.com.my/a/solutions/articles/81000385373
- First, we need to kill all active connection/processes of the database
-
- Right-click your server instance in Object Explorer, and then select New Query
- 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);
- Execute query and all active connection and processes of the database has been terminated
- Now proceed to set the database access from Single User to Multi-User
- Right-click on the ‘Single User Mode’ database and select Properties
- Go to Options and scroll to bottom
- Under category ‘State’, change ‘Restrict Access’ from ‘SINGLE_USER’ to ‘MULTI_USER’ and click okay
Method 2:
- Connect your database with SSMS (SQL Server Management Studio)
https://support.qne.com.my/a/solutions/articles/81000385373
- Right-click your server instance in Object Explorer, and then select New Query
- 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
- 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
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article