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

Question

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/

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;

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

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.