ALTER DATABASE failed because a lock could not be placed on database ‘DBNAME’

Question

I'm using SQL Server 2008 R2. My situation appears to be different to others link, link as I have no active connections to that database as reported by sp_who2.

The physical .MDF and .LDF files are present but I cannot take the database offline or anything. Any ideas?

score 0 · Answer 1 · answered Dec 03 '17 at 01:13

0

You can mandate the priority of your transaction with regards to deadlocks...

SET DEADLOCK_PRIORITY 10

So put that at the top of your alter DB statement.

USE [master]
SET DEADLOCK_PRIORITY 10
ALTER DATABASE
...
GO

answered Dec 03 '17 at 01:13

S3S

24,809
5
26
45

Thanks I will try that.... @charithsuminda its a production server so I am attempting to find a solution that does not restart it. – TheEdge Dec 03 '17 at 05:16

score 0 · Answer 2 · answered Dec 03 '17 at 03:08

0

Can you restart the Sql server service and try to do it as soon as it started. I had a simillar issue but solve by doing this.

answered Dec 03 '17 at 03:08

charithsuminda

341
2
9

score 0 · Answer 3 · answered Dec 03 '20 at 01:33

0

Restart Sql Service or Restart Sql Server Machine we had simillar issue few months ago

answered Dec 03 '20 at 01:33

charithsuminda

341
2
9

ALTER DATABASE failed because a lock could not be placed on database ‘DBNAME’

3 Answers3