Hi there! In this article we are going to discuss how to resolve the error “The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)”
The Complete error message while rename fails for exclusive lock looks like below: Refer How To Rename SQL Server Database
Why the Error Message:
You need to have exclusive lock on the database to rename the db. Exclusive lock means there are no other database connections open/using the database. SQL Engine is smart enough not to allow database name change while other users are accessing the database.
Query to Resolve Database could not be exclusively locked:
Following query will switch the database from Multi User mode to Single User mode, then rename the database and then set it back to Multi User mode.
USE master GO ALTER DATABASE [DatabaseName] --Change The Database Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE [DatabaseName] MODIFY NAME = [NewDatabaseName] GO ALTER DATABASE [NewDatabaseName] --Put the new database name SET MULTI_USER WITH ROLLBACK IMMEDIATE
Point often overlooked in the above query is the final statement, where you need to put the new database name.
To demonstrate please find the following Example for database named “SQLGeeksPro” renaming to “SQLGeeksPro_NewName”
