SQL Server Database Stuck Single User

wh6knrhe 于 2023-03-11 发布在 SQL Server

关注(0)|答案(4)|浏览(200)

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 ?

sql-server

来源：https://stackoverflow.com/questions/59611421/sql-server-database-stuck-single-user

4条答案

按热度按时间

imzjd6km1#

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.

赞(0）回复(0）举报 2023-03-11

vvppvyoh2#

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

赞(0）回复(0）举报 2023-03-11

sc4hvdpw3#

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

赞(0）回复(0）举报 2023-03-11

0sgqnhkj4#

This worked for me.
USE Master
ALTER DATABASE [Your Database]
SET MULTI_USER WITH ROLLBACK IMMEDIATE

赞(0）回复(0）举报 2023-03-11