ALTER DATABASE failed because a lock could not be placed on database ‘dbname’. Try again later.

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s