The database could not be exclusively locked to rename a database in SQL Server Management Studio

I am trying to rename a database in SQL Server Management Studio.but unfortunately, I got the following error:

The database could not be exclusively locked to perform the operation

RenameDB

Cause:

The database could not be exclusively locked to rename the database because of the database is in MULTI_USER Mode.

Solution: 

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

  • Open Management Studio.

SSMS welcome

  • 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 database name should now be changed to the new name.
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 warning.

Enjoy 🙂

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s