At times, we may need to take a database offline. And there is a chance that we encounter an error message like below.
How do we solve this issue and take the database offline?
Msg 5061, Level 16, State 1, Line 1 ALTER DATABASE failed because a lock could not be placed on database 'DBNAME'. Try again later.
If there are any connections open, then there is a lock on the database while getting the database into Single user mode/taking offline.
To resolve the issue, as a first step you need to kill all the sessions on the database followed by the ALTER database statement.
USE master; DECLARE @killSessions varchar(8000) = ''; SELECT @killSessions = @killSessions + 'kill ' + CONVERT(varchar(5), spid) + ';' FROM master..sysprocesses WHERE dbid = db_id('dbname') EXEC(@killSessions); ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE dbname SET OFFLINE WITH ROLLBACK IMMEDIATE GO ALTER DATABASE dbname SET MULTI_USER;