Database in Single User mode

Created by QNE Software Sdn bhd, Modified on Wed, 21 Jul, 2021 at 8:31 AM by QNE Software Sdn bhd

 

Scenario 

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

Solution 

Method 1:

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

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

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

  1. 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);

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

  1. Go to Options and scroll to bottom
  2. 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 

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

  1. 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

  1. 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

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article