ALTER DATABASE 失败，因为无法对数据库加锁

发布于 2024-10-11 20:36:03 字数 716 浏览 4 评论 0原文

我需要重新启动数据库，因为某些进程无法正常工作。我的计划是让它离线然后再次上线。

我试图在 Sql Server Management Studio 2008 中执行此操作：

use master;
go
alter database qcvalues
set single_user
with rollback immediate;
alter database qcvalues
set multi_user;
go

我收到这些错误：

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.

我做错了什么？

原文

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:

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

瞳孔里扚悲伤 2024-10-18 20:36:03

收到错误后，运行

EXEC sp_who2

在列表中查找数据库。连接可能未终止。如果发现任何与数据库的连接，请运行

KILL <SPID>

，其中是连接到数据库的会话的 SPID。

删除与数据库的所有连接后尝试您的脚本。

不幸的是，我不知道您遇到此问题的原因，但这里有一个链接，显示该问题已发生在其他地方。

http://www.geakeit.co.uk/2010/12/11/sql-take-offline-fails-alter-database-failed-because-a-lock-could-not-错误5061/

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.

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.

http://www.geakeit.co.uk/2010/12/11/sql-take-offline-fails-alter-database-failed-because-a-lock-could-not-error-5061/

回复收藏 0 原文

强者自强 2024-10-18 20:36:03

我通过执行以下操作成功重现了此错误。

连接 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

连接 2 和 3

set lock_timeout 5;

ALTER DATABASE TESTING123 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

I managed to reproduce this error by doing the following.

Connection 1 (leave running for a couple of minutes)

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

set lock_timeout 5;

ALTER DATABASE TESTING123 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

回复收藏 0 原文

策马西风 2024-10-18 20:36:03

我会在这里添加这一点，以防有人像我一样幸运。

查看 sp_who2 进程列表时，请注意不仅为受影响的数据库运行的进程，还为 master 运行的进程。就我而言，阻止数据库的问题与启动 xp_cmdshell 的存储过程有关。

检查 master 数据库是否有任何处于 KILL/RollBack 状态的进程。

SELECT *
FROM sys.sysprocesses
WHERE cmd = 'KILLED/ROLLBACK'

如果您遇到同样的问题，仅 KILL 命令可能无济于事。
您可以重新启动SQL Server，或者更好的方法是在SQL Server操作系统的Windows进程下找到cmd.exe并将其杀死。

I will add this here in case someone will be as lucky as me.

When reviewing the sp_who2 list 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.

Check if you have any processes in KILL/RollBack state for master database

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.

回复收藏 0 原文

尹雨沫 2024-10-18 20:36:03

如果它是“过渡中”，请尝试此操作...

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

Try this if it is "in transition" ...

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

回复收藏 0 原文

楠木可依 2024-10-18 20:36:03

只是补充我的两分钱。我将自己置于同样的情况，同时搜索数据库登录成功运行该语句所需的最低权限：

ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE

当使用 sysadmin 执行时，ALTER 语句似乎成功完成 登录，但在“仅”具有有限权限的登录下执行时，它需要连接清理部分，例如：

ALTER ANY DATABASE

PS 我花了几个小时试图弄清楚为什么“ALTER DATABASE..”在以下情况下不起作用在具有 dbcreator 角色 + ALTER ANY DATABASE 权限的登录名下执行。这是我的 MSDN 线程！

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:

ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE

It seems that the ALTER statement completes successfully, when executed with a sysadmin login, but it requires the connections cleanup part, when executed under a login which has "only" limited permissions like:

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 DATABASE privileges. Here's my MSDN thread!

回复收藏 0 原文