SQL Server 2008 R2卡在单用户模式下 [英] SQL Server 2008 R2 Stuck in Single User Mode

问题描述

已经在本地数据库上执行了数据库部署（来自VS SQL Server数据库项目），但失败了，该数据库处于保留了单用户模式的状态（部署以单用户模式运行） 。

Having executed a DB deploy (from a VS SQL Server database project) on a local database, which failed, the database has been left in a state where it has single user mode left on (the deploy runs as single user mode).

当我从SSMS连接到它并尝试以下操作时：

When I connect to it from SSMS and try something like the following:

ALTER DATABASE MyDatabase
SET MULTI_USER;
GO

我收到错误：

目前无法更改数据库'MyDatabase'的状态或选项。数据库处于单用户模式，并且当前有用户连接到数据库。

Changes to the state or options of database 'MyDatabase' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

我尝试使数据库脱机，这是SSMS告诉我成功了，但实际上并没有做任何事情。到目前为止，我只能通过删除并重新创建数据库来解决此问题（这可以，因为它只是本地测试数据库）。但是，我希望能够重置状态。

I tried taking the database offline, which SSMS tells me succeeds, but it doesn't appear to actually do anything. So far, I've only been able to get around this by dropping and recreating the database (which is kind of okay, because it's only a local test database). However, I'd like to be able to reset the status.

如何说服SQL Server使该数据库脱离单用户模式？

How can I convince SQL Server to take this database out of single user mode?

推荐答案

在主数据库中的以下查询中首次运行

In first run following query in master database

exec sp_who

如果找不到元凶，请尝试

If you can't find the culprit, try

SELECT request_session_id FROM sys.dm_tran_locks 
WHERE resource_database_id = DB_ID('YourDatabase')

然后使用以下查询杀死使用数据库的所有进程：

Then kill all process that use your database with following query:

KILL spid

然后运行以下查询：

USE Master
ALTER DATABASE YourDatabase SET MULTI_USER

这篇关于SQL Server 2008 R2卡在单用户模式下的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！