SQL Server Database Stuck Single User

Question

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 ?

Answer 1

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