Rename SQL Server database: The database could not be exclusively locked

In this post, we’ll learn how to fix “The database could not be exclusively locked to perform the operation” error that you may encounter when renaming a database in SQL Server.

You might also like to read Database is not accessible due to a Single User mode


The database could not be exclusively locked to perform the operation

When I tried to rename SQL Server database using SQL Management Studio by clicking on the database name then click F2. I got the below error:

Rename failed for Database
The database could not be exclusively locked to perform the operation

The database could not be exclusively locked to perform the operation

Why I can’t rename SQL Server database?

Simply, you got “Rename database failed” error when you tried to rename SQL Server database because you can’t rename a database in MULTI_USER Mode.

How to rename SQL Server Database?

To rename the database you must first set the database to a Single-user mode that used for maintenance actions.
Single-user mode allows only one user at a time can access the database and is generally used for maintenance actions.

  • Open SQL Sever Management Studio.
  • Connect To SQL Server.
Connect to SSMS
  • From Toolbar, Click on New Query.
  • Paste the following query by replacing your entries.
ALTER DATABASE DB -- old Database Name
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE DB --old Database Name
MODIFY NAME = DB_V2 --New Name
GO
ALTER DATABASE DB_V2 -- New Name
SET MULTI_USER
GO

The above query will do the following:

  1. Set the database to set SINGLE_USER mode.
  2. Modify the database name.
  3. Set the database back to MULTI_USER mode.


Note: If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without any warning.


Conclusion

If you would like to rename a database in SQL Server, you should first set its Mode to SINGLE_USER to allow the maintenance mode, otherwise, you will get “The database could not be exclusively locked to perform the operation“.

Applies To
  • SQL Server 2016.
  • SQL Server 2014.
  • SQL Server 2012.
  • SQL Server 2008.

See Also

Leave a Reply