Error on renaming database in SQL Server 2008 R2

Question

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?

There's nothing wrong with the query - the error is telling you that other connections are connected to the database, so you're not allowed to rename it at this time. — Damien_The_Unbeliever, May 22 '13 at 06:55
If you're doing this from SSMS, make sure you don't have a query window open against that db, as that's a separate connection that places a lock on the db. — jleach, Feb 19 '16 at 09:17

score 413 · Accepted Answer · edited May 23 '17 at 11:47

413

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

edited May 23 '17 at 11:47

Community

1
1

answered May 22 '13 at 07:02

Squid

4,560
1
12
8

2

is `WITH ROLLBACK IMMEDIATE` necessary. If I don't use it at all, will it cause problems? – user13892 Sep 06 '19 at 17:53
2

Bit late to the party, but to answer this question: yes, you should use `WITH ROLLBACK IMMEDIATE` while altering a database that other users might be operating on, in order to ensure the integrity of these operations. But it is not really necessary when setting the database back to MULTI_USER mode again since the database is already at SINGLE_USER mode and you are the only user able to run any transactions anyway. – Hakan Yildizhan Mar 29 '20 at 18:00

score 79 · Answer 2 · answered Jun 12 '14 at 06:00

79

Set the database to single mode:

ALTER DATABASE dbName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Try to rename the database:

ALTER DATABASE dbName MODIFY NAME = NewName

Set the database to Multiuser mode:

ALTER DATABASE NewName
SET MULTI_USER WITH ROLLBACK IMMEDIATE

answered Jun 12 '14 at 06:00

Samiey Mehdi

9,184
18
49
63

@SamieyMehdi Should I use `WITH ROLLBACk IMMEDIATE` for multiuser? – BendEg Oct 20 '16 at 08:48

score 58 · Answer 3 · edited Mar 19 '20 at 00:20

58

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.

edited Mar 19 '20 at 00:20

Pang

9,564
146
81
122

answered Apr 14 '15 at 17:33

Justin Woodmancy

842
6
11

3

Quick and easy! – ani627 Mar 17 '17 at 11:41
That's perfect. Working with SQL Server 2017 – Adam Macierzyński Mar 08 '19 at 13:09

score 22 · Answer 4 · edited May 23 '17 at 12:34

22

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

edited May 23 '17 at 12:34

Community

1
1

answered May 22 '13 at 06:59

Andrey Gordeev

30,606
13
135
162

score 7 · Answer 5 · answered Feb 17 '15 at 15:29

7

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

answered Feb 17 '15 at 15:29

t_plusplus

4,079
5
45
60

score 5 · Answer 6 · edited Mar 19 '20 at 00:22

5

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

edited Mar 19 '20 at 00:22

Pang

9,564
146
81
122

answered May 22 '13 at 06:57

Dhwani

7,484
17
78
139

score 5 · Answer 7 · answered Mar 01 '17 at 23:12

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

slayernoah · Answer 8 · 2014-10-05T02:14:33.527

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

score 2 · Answer 9 · answered Jan 28 '22 at 05:07

2

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.

answered Jan 28 '22 at 05:07

remondo

318
2
7

score 0 · Answer 10 · answered Feb 14 '17 at 19:45

0

Another way to close all connections:

Administrative Tools > View Local Services

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

answered Feb 14 '17 at 19:45

Chronozoa

310
6
10

score -2 · Answer 11 · edited Jan 30 '17 at 09:58

-2

use master

ALTER DATABASE BOSEVIKRAM SET SINGLE_USER WITH ROLLBACK IMMEDIATE  

exec sp_renamedb 'BOSEVIKRAM','BOSEVIKRAM_Deleted'

ALTER DATABASE BOSEVIKRAM_Deleted SET MULTI_USER

edited Jan 30 '17 at 09:58

Barry Michael Doyle

9,333
30
83
143

answered Jan 30 '17 at 08:59

KamalDeep

791
5
8

1

This is the exact same answer as Squid's – reggaeguitar Aug 22 '19 at 20:41

Error on renaming database in SQL Server 2008 R2

11 Answers11