1

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 ?

Coskun Ozogul
  • 2,389
  • 1
  • 20
  • 32
  • This question is a better fit for [dba.se] – Igor Jan 06 '20 at 11:38
  • I will post it there too. Thanks – Coskun Ozogul Jan 06 '20 at 11:40
  • If you are trying to restore a backup just take the database offline. WAY cleaner than dealing with this multi and single user bologna. Your problem is most likely that your SSMS Object Explorer is holding the connection. If you right click>disconnect and then run your query you should be good. – Jacob H Jan 06 '20 at 12:25
  • Does this answer your question? [Set database from SINGLE USER mode to MULTI USER](https://stackoverflow.com/questions/14652923/set-database-from-single-user-mode-to-multi-user) – Sire Aug 11 '22 at 12:03

4 Answers4

3

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

CaffeinatedCod3r
  • 821
  • 7
  • 14
  • The OP stated it's already in `SINGLE_USER` mode; trying to change it to it again won't change anything. – Thom A Jan 06 '20 at 12:04
  • Documentation said that using `ROLLBACK IMMEDIATE` will close all the connections.So i thought that might be helpful because OP stated that database is in use – CaffeinatedCod3r Jan 09 '20 at 08:30
3

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

Coskun Ozogul
  • 2,389
  • 1
  • 20
  • 32
3

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.

Greg Low
  • 1,526
  • 1
  • 4
  • 4
1

This worked for me.

USE Master
ALTER DATABASE [Your Database]
SET MULTI_USER WITH ROLLBACK IMMEDIATE
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Shiva Darsh
  • 11
  • 3
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 15 '23 at 00:11
  • In order for this to work, SQL Server service should be restarted first. – STLDev Jun 28 '23 at 02:41