Cry about...
SQL Server Troubleshooting

---

Rename failed for Database 'my-database'

---

Symptom:

When attempting to rename a database using SQL Server Management Studio the following error is produced:

Unable to rename MyDatabase. (ObjectExplorer)

Additional information:

⌊ Rename failed for Databse 'MyDatabase' (Microsoft.SqlServer.Smo)

⌊ An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

⌊ The databse could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

Where 'MyDatabase' is the name of the database you were trying to rename.

Cause:

SqlServer will not allow a database to be renamed unless the database is in single user mode.

Remedy:

Set the database in single user mode, rename it and then change it back to multi-user. This can be achieved using:

alter database old-db-name set single_user with rollback immediate

go

sp_renamedb @dbname='old-db-name', @newname='new-db-name'

go

alter database new-db-name set multi_user

go

Substituting the name of your database for 'old-db-name' and the new name for your database for 'new-db-name' in the above.

If you find that it hangs (or timesout) when setting the database to single user mode, this this almost certainly because someone is currently connected to the database. In which case try:

alter database old-db-name set single_user with rollback immediate

as this will set the database in single-user mode immediatly, rolling back any pending transactions.

---

These notes are believed to be correct for SQL Server 2008 R2 and may apply to other versions as well.