Problem statement

As a DBA, you will be facing the error message “ALTER DATABASE failed because a lock could not be placed on database ‘dbname’. Try again later.” often for certain actions like taking database offline/setting single user etc.

Analysis

This is due to other processes locking your current operations.

Resolution

1. Find out the query which is blocking the current operation using the below query
Live monitoring query

2. Kill the process and take the database in SINGLE_USER

KILL 191 --session id from the first query related to the database
USE MASTER
ALTER DATABASE DB905 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

3. Take the database offline

ALTER DATABASE db905 SET OFFLINE WITH ROLLBACK IMMEDIATE