英文:
SQL Server Database Stuck Single User
问题
我在Windows Server 2016上运行SQL Server 2016。
数据库被卡在单用户模式。
我尝试执行以下操作：
ALTER DATABASE MyDatabase
SET MULTI_USER;
但它显示数据库正在使用中。
我尝试使用以下命令查找spID：
exec sp_who
我找到spid 57正在使用该数据库，
尝试使用以下命令终止spID：
KILL 57
但它显示：进程ID 57不是活动进程ID。
我真的很困惑！
我甚至无法重命名或删除数据库。
我尝试了所有这些但都没有成功：
https://stackoverflow.com/questions/24608702/sql-server-2008-r2-stuck-in-single-user-mode
有什么建议吗？
英文:
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 :
https://stackoverflow.com/questions/24608702/sql-server-2008-r2-stuck-in-single-user-mode
Any idea please ?
答案1
得分: 3
从官方文档中，您可以尝试稍微更改它，删除只读部分
ALTER DATABASE [database_name]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [database_name]
SET MULTI_USER;
GO
文档链接：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
英文:
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
答案2
得分: 3
我找到了解决方法，
我重新启动了SQL服务器服务，重新执行了查询exec sp_who，并找到了另一个spID，这次可以终止它。
谢谢
英文:
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
答案3
得分: 3
这意味着数据库正在使用中，它被设置为单用户模式，而您不是唯一的用户。这种情况的常见原因是SSMS中的对象资源管理器已连接到数据库。关闭与服务器连接的所有内容（甚至重新启动SQL Server服务，如果需要的话），然后再试一次。最坏的情况下，不要使用SSMS。只需使用SQLCMD连接，这样您就知道没有其他连接了。
英文:
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.
答案4
得分: 1
这对我来说有效。
使用 Master
更改数据库 [你的数据库]
设置 MULTI_USER WITH ROLLBACK IMMEDIATE
英文:
This worked for me.
USE Master
ALTER DATABASE [Your Database]
SET MULTI_USER WITH ROLLBACK IMMEDIATE
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论