Every consulting engagement is different and I get to learn new things. In this blog post, I will share what I learned during my recent Comprehensive Database Performance Health Check. During health check to demonstrate one of the scenarios we had created a test database in our environment. However, after the test was complete we wanted to take the database offline so we eventually can drop it or bring it online if needed, however, as soon as we ran the command to take database offline, it gave is an error related to alter database failed because a lock could not be placed on database.
If you want to learn how to take the database offline and online, you can read my earlier blog post here: SQL SERVER – T-SQL Script to Take Database Offline – Take Database Online.
If you try to take your database offline and you see the following error:
Msg 5061, Level 16, State 1, Line 1 ALTER DATABASE failed because a lock could not be placed on database 'database name'. Try again later. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.
In that case, you run first the following command try to find your database in the list. It is totally possible due to some reason, your database connection is still active and not terminated.
If you are comfortable you can easily kill the SPID by running following command.
After you get the error, run
Once you kill all the active sessions you can again run the command specified in the blog post and take your database offline using alter database command.
Reference: Pinal Dave (https://blog.sqlauthority.com)