SQL ALTER DATABASE 失败，因为无法在数据库上放置锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4673065/
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
ALTER DATABASE failed because a lock could not be placed on database
提问by JOE SKEET
I need to restart a database because some processes are not working. My plan is to take it offline and back online again.
我需要重新启动数据库，因为某些进程不起作用。我的计划是将其离线并重新上线。
I am trying to do this in Sql Server Management Studio 2008:
我正在尝试在 Sql Server Management Studio 2008 中执行此操作：
use master;
go
alter database qcvalues
set single_user
with rollback immediate;
alter database qcvalues
set multi_user;
go
I am getting these errors:
我收到这些错误：
Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'qcvalues'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5061, Level 16, State 1, Line 4
ALTER DATABASE failed because a lock could not be placed on database 'qcvalues'. Try again later.
Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.
What am I doing wrong?
我究竟做错了什么？
回答by bobs
After you get the error, run
得到错误后，运行
EXEC sp_who2
Look for the database in the list. It's possible that a connection was not terminated. If you find any connections to the database, run
在列表中查找数据库。连接可能未终止。如果您发现与数据库的任何连接，请运行
KILL <SPID>
where <SPID>is the SPID for the sessions that are connected to the database.
<SPID>连接到数据库的会话的 SPID在哪里。
Try your script after all connections to the database are removed.
在删除与数据库的所有连接后尝试您的脚本。
Unfortunately, I don't have a reason why you're seeing the problem, but here is a link that shows that the problem has occurred elsewhere.
不幸的是，我不知道您看到问题的原因，但这里有一个链接，表明问题发生在其他地方。
回答by Martin Smith
I managed to reproduce this error by doing the following.
我通过执行以下操作设法重现了此错误。
Connection 1 (leave running for a couple of minutes)
连接 1（保持运行几分钟）
CREATE DATABASE TESTING123
GO
USE TESTING123;
SELECT NEWID() AS X INTO FOO
FROM sys.objects s1,sys.objects s2,sys.objects s3,sys.objects s4 ,sys.objects s5 ,sys.objects s6
Connections 2 and 3
连接 2 和 3
set lock_timeout 5;
ALTER DATABASE TESTING123 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
回答by Watki02
Try this if it is "in transition" ...
如果它处于“过渡中”，请尝试此操作...
http://learnmysql.blogspot.com/2012/05/database-is-in-transition-try-statement.html
http://learnmysql.blogspot.com/2012/05/database-is-in-transition-try-statement.html
USE master
GO
ALTER DATABASE <db_name>
SET OFFLINE WITH ROLLBACK IMMEDIATE
...
...
ALTER DATABASE <db_name> SET ONLINE
回答by Alina
I will add this here in case someone will be as lucky as me.
我会在这里添加这个以防有人像我一样幸运。
When reviewing the sp_who2list of processes note the processes that run not only for the effected database but also for master. In my case the issue that was blocking the database was related to a stored procedure that started a xp_cmdshell.
查看sp_who2进程列表时，请注意不仅为受影响的数据库而且还为master运行的进程。就我而言，阻塞数据库的问题与启动 xp_cmdshell 的存储过程有关。
Check if you have any processes in KILL/RollBackstate for masterdatabase
检查master数据库是否有处于KILL/RollBack状态的进程
SELECT *
FROM sys.sysprocesses
WHERE cmd = 'KILLED/ROLLBACK'
If you have the same issue, just the KILL command will probably not help. You can restarted the SQL server, or better way is to find the cmd.exe under windows processes on SQL server OS and kill it.
如果您遇到同样的问题，仅使用 KILL 命令可能无济于事。您可以重新启动SQL Server，或者更好的方法是在SQL Server OS上的windows进程下找到cmd.exe并杀死它。
回答by mitix
In rare cases (e.g., after a heavy transaction is commited) a running CHECKPOINT system process holding a FILE lock on the database file prevents transition to MULTI_USER mode.
在极少数情况下（例如，在提交大量事务之后），正在运行的 CHECKPOINT 系统进程在数据库文件上持有 FILE 锁会阻止转换到 MULTI_USER 模式。
回答by user123456789
In my scenario, there was no process blocking the database under sp_who2. However, we discovered because the database is much larger than our other databases that pending processes were still running which is why the database under the availability group still displayed as red/offline after we tried to 'resume data'by right clicking the paused database.
在我的场景中，没有进程阻塞 sp_who2 下的数据库。但是，我们发现由于数据库比其他数据库大得多，挂起的进程仍在运行，这就是为什么在我们尝试通过右键单击暂停的数据库来“恢复数据”后，可用性组下的数据库仍显示为红色/离线的原因。
To check if you still have processes running just execute this command: select percent complete from sys.dm_exec_requests where percent_complete > 0
要检查您是否仍有进程在运行，只需执行以下命令： select percent complete from sys.dm_exec_requests where percent_complete > 0
回答by Marty
In SQL Management Studio, go to Security -> Logins and double click your Login. Choose Server Roles from the left column, and verify that sysadmin is checked.
在 SQL Management Studio 中，转到安全 -> 登录名并双击您的登录名。从左栏中选择 Server Roles，并验证是否选中了 sysadmin。
In my case, I was logged in on an account without that privilege.
就我而言，我登录的帐户没有该权限。
HTH!
哼！
回答by user3749524
Killing the process ID worked nicely for me. When running "EXEC sp_who2" Command over a new query window... and filter the results for the "busy" database , Killing the processes with "KILL " command managed to do the trick. After that all worked again.
杀死进程 ID 对我来说效果很好。在新的查询窗口上运行“EXEC sp_who2”命令......并过滤“繁忙”数据库的结果时，使用“KILL”命令杀死进程设法做到了这一点。在那之后，一切又重新开始了。
回答by Veselin Z.
Just to add my two cents. I've put myself into the same situation, while searching the minimum required privileges of a db login to run successfully the statement:
只是为了加上我的两分钱。我让自己陷入了同样的情况，同时搜索了 db 登录所需的最低权限以成功运行该语句：
ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE
It seems that the ALTER statement completes successfully, when executed with a sysadminlogin, but it requires the connections cleanup part, when executed under a login which has "only" limited permissions like:
当使用sysadmin登录名执行时，似乎 ALTER 语句成功完成，但在“仅”具有有限权限的登录名下执行时，它需要连接清理部分，例如：
ALTER ANY DATABASE
P.S. I've spent hours trying to figure out why the "ALTER DATABASE.." does not work when executed under a login that has dbcreator role + ALTER ANY DATABASEprivileges. Here's my MSDN thread!
PS 我花了几个小时试图弄清楚为什么在具有dbcreator 角色 + ALTER ANY DATABASE权限的登录名下执行时“ALTER DATABASE..”不起作用。这是我的MSDN 线程！
回答by Geoff Dawdy
I know this is an old post but I recently ran into a very similar problem. Unfortunately I wasn't able to use any of the alter database commands because an exclusive lock couldn't be placed. But I was never able to find an open connection to the db. I eventually had to forcefully delete the health state of the database to force it into a restoring state instead of in recovery.
我知道这是一个旧帖子，但我最近遇到了一个非常相似的问题。不幸的是，我无法使用任何更改数据库命令，因为无法放置排它锁。但我一直无法找到与数据库的开放连接。我最终不得不强行删除数据库的健康状态以强制它进入恢复状态而不是恢复状态。
