데이터베이스에 잠금을 배치 할 수 없으므로 ALTER DATABASE가 실패했습니다.

데이터베이스에 잠금을 배치 할 수 없으므로 ALTER DATABASE가 실패했습니다.

---

일부 프로세스가 작동하지 않기 때문에 데이터베이스를 다시 시작해야합니다. 내 계획은 오프라인 상태로 만들고 다시 온라인 상태로 만드는 것입니다.

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.

내가 뭘 잘못하고 있죠?

---

오류가 발생하면 다음을 실행하십시오.

EXEC sp_who2

목록에서 데이터베이스를 찾으십시오. 연결이 종료되지 않았을 수 있습니다. 데이터베이스에 대한 연결을 찾으면 다음을 실행하십시오.

KILL <SPID>

여기서 <SPID>데이터베이스에 연결된 세션의 SPID는 어디 입니까?

데이터베이스에 대한 모든 연결이 제거 된 후 스크립트를 시도하십시오.

불행히도, 왜 당신이 문제를보고 있는지 이유가 없지만, 여기 다른 곳에서 문제가 발생했음을 보여주는 링크가 있습니다.

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

---

다음을 수행 하여이 오류를 재현했습니다.

연결 1 (2 분 동안 그대로 두십시오)

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;

---

"전환 중"인 경우 시도하십시오 ...

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

---

누군가 나처럼 운이 좋을 경우에 이것을 추가 할 것입니다.

sp_who2 프로세스 목록을 검토 할 때 영향을받는 데이터베이스뿐만 아니라 master 에도 실행되는 프로세스를 기록하십시오 . 필자의 경우 데이터베이스를 차단하는 문제는 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.

---

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.

In my case, I was logged in on an account without that privilege.

HTH!

---

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.

---

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!

---

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.

---

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.

---

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.

To check if you still have processes running just execute this command: select percent complete from sys.dm_exec_requests where percent_complete > 0

참고URL : https://stackoverflow.com/questions/4673065/alter-database-failed-because-a-lock-could-not-be-placed-on-database