SQL 将数据库从 SINGLE USER 模式设置为 MULTI USER
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14652923/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set database from SINGLE USER mode to MULTI USER
提问by Rahna1970418
I need help with setting a database that was restored in SINGLE_USERmode to MULTI_USER. Every time I run
我需要帮助将在SINGLE_USER模式下恢复的数据库设置为MULTI_USER. 每次我跑
ALTER DATABASE BARDABARD
SET MULTI_USER;
GO
I get this error:
我收到此错误：
Changes to the state or options of database 'BARDABARD' cannot be made at this time.
The database is in single-user mode, and a user is currently connected to it.
此时无法更改数据库“BARDABARD”的状态或选项。
数据库处于单用户模式，当前有用户连接到它。
It needs to be in non-SINGLE_USERmode to set it to another mode, but I can't set the database in any another mode while it is SINGLE_USERmode.
它需要处于非SINGLE_USER模式才能将其设置为另一种模式，但是当它处于模式时我无法将数据库设置为任何其他SINGLE_USER模式。
采纳答案by paul
The “user is currently connected to it” might be SQL Server Management Studio window itself. Try selecting the master database and running the ALTERquery again.
“用户当前已连接到它”可能是 SQL Server Management Studio 窗口本身。尝试选择主数据库并ALTER再次运行查询。
回答by sgeddes
That error message generally means there are other processes connected to the DB. Try running this to see which are connected:
该错误消息通常意味着有其他进程连接到数据库。尝试运行它以查看哪些已连接：
exec sp_who
That will return you the process and then you should be able to run:
这将返回您的过程，然后您应该能够运行：
kill [XXX]
Where [xxx] is the spidof the process you're trying to kill.
其中 [xxx] 是spid您试图杀死的进程的。
Then you can run your above statement.
然后你可以运行上面的语句。
Good luck.
祝你好运。
回答by LG1
You can add the option to rollback your change immediately.
您可以添加选项以立即回滚您的更改。
ALTER DATABASE BARDABARD
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
GO
回答by Div2504
SQL Server 2012:
SQL Server 2012：
right-click on the DB > Properties > Options > [Scroll down] State > RestrictAccess > select Multi_userand click OK.
右键单击DB > Properties > Options > [Scroll down] State > RestrictAccess > select Multi_user并单击确定。
Voila!
瞧！
回答by Azadeh Khojandi
I had the same issue and it fixed by the following steps - reference: http://giladka8.blogspot.com.au/2011/11/database-is-in-single-user-mode-and.html
我有同样的问题，它通过以下步骤修复 - 参考：http: //giladka8.blogspot.com.au/2011/11/database-is-in-single-user-mode-and.html
use master
GO
select
d.name,
d.dbid,
spid,
login_time,
nt_domain,
nt_username,
loginame
from sysprocesses p
inner join sysdatabases d
on p.dbid = d.dbid
where d.name = 'dbname'
GO
kill 56 --=> kill the number in spid field
GO
exec sp_dboption 'dbname', 'single user', 'FALSE'
GO
回答by sunil guragol
This worked fine for me.
这对我来说很好。
Step 1. Right click on database engine, click on activity monitor and see which process is having connection. Kill that particular user and execute the query Immediately.
步骤 1. 右键单击数据库引擎，单击活动监视器并查看哪个进程有连接。杀死该特定用户并立即执行查询。
Step 2.
第2步。
USE [master];
GO
ALTER DATABASE [YourDatabaseNameHere] SET MULTI_USER WITH NO_WAIT;
GO
and refresh the database.
并刷新数据库。
回答by Shane G.
I actually had an issue where my db was pretty much locked by the processes and a race condition with them, by the time I got one command executed refreshed and they had it locked again... I had to run the following commands back to back in SSMS and got me offline and from there I did my restore and came back online just fine, the two queries where:
我实际上遇到了一个问题，我的数据库几乎被进程锁定，并且与它们竞争条件，当我刷新一个命令执行并且他们再次锁定它时......我不得不背靠背运行以下命令在 SSMS 中并让我离线，然后我进行了恢复并重新上线，这两个查询如下：
First ran:
首先运行：
USE master
GO
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('<yourDbName>')
EXEC(@kill);
Then immediately after (in second query window):
然后紧接着（在第二个查询窗口中）：
USE master ALTER DATABASE <yourDbName> SET OFFLINE WITH ROLLBACK IMMEDIATE
Did what I needed and then brought it back online. Thanks to all who wrote these pieces out for me to combine and solve my problem.
做了我需要的，然后把它带回网上。感谢所有为我编写这些文章的人，以结合并解决我的问题。
回答by Chris Halcrow
It may be best to log onto the server directly instead of using SQL Management Studio
最好直接登录服务器而不是使用 SQL Management Studio
Ensure that the account you login as is dbowner for the database you want to set to MULTI_USER. Login as sa (using SQL server authentication) if you can
确保您登录的帐户是要设置为 MULTI_USER 的数据库的 dbowner。如果可以，以 sa 身份登录（使用 SQL 服务器身份验证）
If your database is used by IIS, stop the website and the app pool that use it- this may be the process that's connected and blocking you from setting to MULTI_USER
如果您的数据库被 IIS 使用，请停止使用它的网站和应用程序池- 这可能是连接的进程并阻止您设置为 MULTI_USER
USE MASTER
GO
-- see if any process are using *your* database specifically
SELECT * from master.sys.sysprocesses
WHERE spid > 50 -- process spids < 50 are reserved by SQL - we're not interested in these
AND dbid=DB_ID ('YourDbNameHere')
-- if so, kill the process:
KILL n -- where 'n' is the 'spid' of the connected process as identified using query above
-- setting database to read only isn't generally necessary, but may help:
ALTER DATABASE YourDbNameHere
SET READ_ONLY;
GO
-- should work now:
ALTER DATABASE Appswiz SET MULTI_USER WITH ROLLBACK IMMEDIATE
Refer here if you still have trouble:
如果您仍然遇到问题，请参阅此处：
AS A LAST ALTERNATIVE- If you've tried everything above and you're getting desperate you could try stopping the SQL server instance and start it again
作为最后的选择- 如果您已经尝试了上述所有方法并且您感到绝望，您可以尝试停止 SQL 服务器实例并重新启动它
回答by Rikin Patel
I have just fixed using following steps, It may help you.
我刚刚使用以下步骤修复，它可能对您有所帮助。
Step: 1
第1步
Step: 2
第2步
Step: 3
步骤：3
Step: 4
第四步
Step: 5
步骤：5
Then run following query.
然后运行以下查询。
ALTER DATABASE YourDBName
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
GO
Enjoy...!
享受...！
回答by Jaypal Reddy
The code below has worked for me when I didn't know the specific SPID that was used to change into singleusermode.
当我不知道用于更改为singleuser模式的特定 SPID 时，下面的代码对我有用。
use master
GO
select
d.name,
d.dbid,
spid,
login_time,
nt_domain,
nt_username,
loginame
from sysprocesses p
inner join sysdatabases d
on p.dbid = d.dbid
where d.name = 'dbname'
GO
kill 52 -- kill the number in spid field
GO
exec sp_dboption 'dbname', 'single user', 'FALSE'
GO
