CODEWITHSUNDEEP
sql-server
Coskun Ozogul
Coskun Ozogul

Reputation: 2469

SQL Server Database Stuck Single User

I have a SQL Server 2016 on a Windows Server 2016.

The database is stuck in single user mode.

I try to do this :

  ALTER DATABASE MyDatabase
  SET MULTI_USER;

But it says that the database is in use.

I tried this to find the spID :

 exec sp_who

And I found the spid 57 is using the database,

Tried this to kill the spID 

 KILL 57

But it says : Process ID 57 is not an active process ID.

I am really stuck!

I can't even rename or delete the database.

I tried all of these but, didn't work : SQL Server 2008 R2 Stuck in Single User Mode

Any idea please ?

Upvotes: 2

Views: 13441

Answers (5)

Andrew D
Andrew D

Reputation: 1

It is useful to state here that if this fails it might be due to the fact that you are running a query just by running that set. Restart the service and right click on the database within SSMS select properties and option, then scroll down and change the restricted access from Single-user to multi-user. -AD

Upvotes: 0

Shiva Darsh
Shiva Darsh

Reputation: 21

This worked for me.

USE Master
ALTER DATABASE [Your Database]
SET MULTI_USER WITH ROLLBACK IMMEDIATE

Upvotes: 2

Greg Low
Greg Low

Reputation: 1586

It means that the DB is in use, it's set to single user mode, and you're not that single user. A common cause of that is that Object Explorer in SSMS is connected to the DB. Close everything that's connected to the server (even restart the SQL Server service if you need to), and try again. At worst, don't use SSMS. Just connect with SQLCMD, so you know that nothing else is connected.

Upvotes: 3

Coskun Ozogul
Coskun Ozogul

Reputation: 2469

I found the solution,

I restarted the sql server service, re-execute the query exec sp_who and found another spID and could kill it this time.

Thanks

Upvotes: 3

CaffeinatedCod3r
CaffeinatedCod3r

Reputation: 882

From the Official docs you can try changing it a little bit by removing the read-only part

ALTER DATABASE [database_name]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE [database_name]
SET MULTI_USER;
GO

Docs : https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15#b-setting-the-database-to-read_only

Upvotes: 3

Related Questions