CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Vikram Bose
Vikram Bose

Reputation: 3375

Error on renaming database in SQL Server 2008 R2

I am using this query to rename the database:

ALTER DATABASE BOSEVIKRAM MODIFY NAME = [BOSEVIKRAM_Deleted]

But it shows an error when excuting:

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

Is anything wrong with my query?

Upvotes: 235

Views: 200806

Answers (11)

remondo
remondo

Reputation: 388

For me the reason why I could not rename a database is because there are active connections. I just take the database offline first, ticking the Drop All Active Connections. Then bring it online again and I can rename the database already. Take Offline

enter image description here

Upvotes: 5

Dhwani
Dhwani

Reputation: 7626

That's because someone else is accessing the database. Put the database into single user mode then rename it.

This link might help:
http://msdn.microsoft.com/en-IN/library/ms345378(v=sql.105).aspx

and also:
http://msdn.microsoft.com/en-us/library/ms345378.aspx

Upvotes: 5

Justin Woodmancy
Justin Woodmancy

Reputation: 952

In SQL Server Management Studio (SSMS):

You can also right click your database in the Object Explorer and go to Properties. From there, go to Options. Scroll all the way down and set Restrict Access to SINGLE_USER. Change your database name, then go back in and set it back to MULTI_USER.

Upvotes: 69

K GANGA
K GANGA

Reputation: 51

1.database set 1st single user mode

ALTER DATABASE BOSEVIKRAM SET SINGLE_USER WITH ROLLBACK IMMEDIATE

2.RENAME THE DATABASE

ALTER DATABASE BOSEVIKRAM MODIFY NAME = [BOSEVIKRAM_Deleted]

3.DATABAE SET MULIUSER MODE

ALTER DATABASE BOSEVIKRAM_Deleted SET MULTI_USER WITH ROLLBACK IMMEDIATE

Upvotes: 5

Chronozoa
Chronozoa

Reputation: 310

Another way to close all connections:

Administrative Tools > View Local Services

Stop/Start the "SQL Server (MSSQLSERVER)" service

Upvotes: 0

KamalDeep
KamalDeep

Reputation: 834

use master

ALTER DATABASE BOSEVIKRAM SET SINGLE_USER WITH ROLLBACK IMMEDIATE  

exec sp_renamedb 'BOSEVIKRAM','BOSEVIKRAM_Deleted'

ALTER DATABASE BOSEVIKRAM_Deleted SET MULTI_USER

Upvotes: -3

t_plusplus
t_plusplus

Reputation: 4209

This did it for me: 

USE [master];
GO
ALTER DATABASE [OldDataBaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_renamedb N'OldDataBaseName', N'NewDataBaseName';


-- Add users again
ALTER DATABASE [NewDataBaseName] SET MULTI_USER
GO

Upvotes: 8

slayernoah
slayernoah

Reputation: 4492

Change database to single user mode as shown in the other answers

Sometimes, even after converting to single user mode, the only connection allowed to the database may be in use.

To close a connection even after converting to single user mode try: 

select * from master.sys.sysprocesses
where spid>50 -- don't want system sessions
  and dbid = DB_ID('BOSEVIKRAM')

Look at the results and see the ID of the connection to the database in question.

Then use the command below to close this connection (there should only be one since the database is now in single user mode)

KILL connection_ID

Replace connection_id with the ID in the results of the 1st query

Upvotes: 3

Samiey Mehdi
Samiey Mehdi

Reputation: 9414

  1. Set the database to single mode: 

    ALTER DATABASE dbName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  2. Try to rename the database:

    ALTER DATABASE dbName MODIFY NAME = NewName

  3. Set the database to Multiuser mode:

    ALTER DATABASE NewName
SET MULTI_USER WITH ROLLBACK IMMEDIATE

Upvotes: 89

Squid
Squid

Reputation: 4810

You could try setting the database to single user mode.

https://stackoverflow.com/a/11624/2408095

use master
ALTER DATABASE BOSEVIKRAM SET SINGLE_USER WITH ROLLBACK IMMEDIATE    
ALTER DATABASE BOSEVIKRAM MODIFY NAME = [BOSEVIKRAM_Deleted]
ALTER DATABASE BOSEVIKRAM_Deleted SET MULTI_USER

Upvotes: 438

Andrey Gordeev
Andrey Gordeev

Reputation: 32459

Try to close all connections to your database first:

use master
ALTER DATABASE BOSEVIKRAM SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

ALTER DATABASE BOSEVIKRAM MODIFY NAME = [BOSEVIKRAM_Deleted]

ALTER DATABASE BOSEVIKRAM_Deleted SET MULTI_USER

Taken from here

Upvotes: 23

Related Questions