If you’ve ever used Object Explorer (in SSMS) in order to rename a database, you may have come across this error message from Management Studio:
which says:
Unable to rename [database]. (ObjectExplorer)
Additional information:
--> Rename failed for Databse '[databse]'.
(Microsoft.SqlServer.Smo)
--> An exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--> The database could not be exclusively locked to perform the operation.
(Microsoft SQL Server, Error: 5030)
Management Studio is telling you that there are connections that it doesn’t want to mess with.
Or when using sp_renamedb from a query window, you might see this similar error:
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
If you really really want to rename the database and don’t care at all about in-flight transactions, then use this script/template I recently came up with:
A Forceful Script:
ALTER DATABASE [old_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE [old_name] MODIFY NAME = [new_name] GO ALTER DATABASE [new_name] SET MULTI_USER GO |
Caveat:
Remember that this script failed kindegarten for not playing well with others. So use this script only on QA and Dev boxes. And if you’re going to do something on a production box, make sure you do this only during scheduled downtime.
One More Interesting Thing:
A friend of mine stored this script in One Note and found that when it came to use it, the script failed because One Note had replaced some of the white space with non breaking spaces!
This issue is a One Note issue and applies to all scripts and code, not just T-SQL scripts.
