1

I have a database 'My Database' which I would like to rename so that there is no white space. I tried to rename it using

use master
exec sp_renamedb 'I 3 SCI Study','I3SciStudy'

and was greeted with the error

Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.

This server is my local machine and I have no other query windows open but the window in which I ran the rename query. Is there some sort of close connection command that I need to run before I can rename the database?

wootscootinboogie
  • 8,461
  • 33
  • 112
  • 197
  • 3
    See [this question](http://stackoverflow.com/questions/11014343/how-to-rename-database-in-multi-user-mode) and [this one](http://stackoverflow.com/questions/525185/sql-server-database-locked). And note that `sp_renamedb` is [deprecated](http://msdn.microsoft.com/en-us/library/ms186217.aspx). – Pondlife Apr 05 '13 at 17:53
  • 1
    Try putting the database in single user mode – paparazzo Apr 05 '13 at 18:10
  • Can you stop the Sql Server SERVICE (Control Panel / Admin Tools / Service) and then try it. That would shut down all connections for sure. – granadaCoder Apr 05 '13 at 19:41
  • @granadaCoder I ended up just detaching and reattaching the database and the renaming the database and that worked. – wootscootinboogie Apr 05 '13 at 19:42

3 Answers3

0

Try this command, but caution is advised:

USE master;
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE [dbname] SET MULTI_USER;
GO
AdamL
  • 12,421
  • 5
  • 50
  • 74
0

Also you can interrogate information about currently active lock manager resources.

SELECT *
FROM sys.dm_tran_locks DTL
WHERE DTL.[resource_database_id] = DB_ID()

Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. You will see not only your request on current database(most likely with resoure_type DATABASE). It is impossible to change a database name while these resources are locked

Aleksandr Fedorenko
  • 16,594
  • 6
  • 37
  • 44
-1

Use SSMS to rename the database, you shouldn't have any problem doing it that way.

Dustin
  • 147
  • 6