rename database but error for exclusive lock

5 Apr 2005 2:01 PM

ilias Mintidis

Hello,

I want to rename a database and i am using sp_renamedb
'olddatabasename', 'newdatabasename' but i receive errors.
database could not be exclusively locked to perform the operation

what is that i can do about that?

*** Sent via Developersdex http://www.developersdex.com ***

5 Apr 2005 2:06 PM

Hari Prasad

Hi,

While renaming, No users should be connected to the database. You can turn
on the database to single user mode by executing the below command.

ALTER DATABASE <DBNAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB <oldname>,<Newname>
Go
ALTER DATABASE <NEWDBNAME> SET MULTI_USER -- set back to multi user
GO

Thanks
Hari
SQL Server MVP

Show quoteHide quote

"ilias Mintidis" <imint***@sebh.org> wrote in message
news:%23x1mmfeOFHA.1396@TK2MSFTNGP10.phx.gbl...
> Hello,
>
> I want to rename a database and i am using sp_renamedb
> 'olddatabasename', 'newdatabasename' but i receive errors.
> database could not be exclusively locked to perform the operation
>
> what is that i can do about that?
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

5 Apr 2005 2:32 PM

ilias Mintidis

great ,thanks ,one more question in this.
The server has also another database loaded,which isc the LIVE
database.i am doing the rename of a test database,so i can restore an
additional database from .bak file.
do i have to specify which database i put in single user mode?Do i do
that by selecting the test database from the drop down in sql analyser?

*** Sent via Developersdex http://www.developersdex.com ***

5 Apr 2005 6:55 PM

Hari Prasad

Hi Illias,

No need to select the database name. From master you can execute the ALTER
DATABASE statement to set the database to single user mode.

Thanks
Hari
SQL Server MVP

Show quoteHide quote

"ilias Mintidis" wrote:

>
> great ,thanks ,one more question in this.
> The server has also another database loaded,which isc the LIVE
> database.i am doing the rename of a test database,so i can restore an
> additional database from .bak file.
> do i have to specify which database i put in single user mode?Do i do
> that by selecting the test database from the drop down in sql analyser?
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>