Error Message: “The database could not be exclusively locked to perform the operation.” in SQL Server

Recently we encountered an error message as below while doing a rename database. So, let us look at the steps that we can use to overcome the issue with this post.

The database could not be exclusively locked to perform the operation.

We were trying to rename a database in one of our lower environment as below and ended up with the error message.

ALTER DATABASE dbname MODIFY NAME = dbname_new

Steps to resolve

  1. Take the database to single_user mode
  2. Rename the database
  3. Take the renamed database to multi_user mode

Script

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE dbname MODIFY NAME = dbname_new
GO
ALTER DATABASE dbname_new SET MULTI_USER WITH ROLLBACK IMMEDIATE

If you enjoyed this blog post, please share it with your friends!

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