CODEWITHSUNDEEP
sqldatabasesql-server-2008
None
None

Reputation: 5670

Error In Database Renaming

I need to rename one of my database and tried a query like this

ALTER DATABASE Test MODIFY NAME = NewTest

But this throws an error 

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

Can any one give me any suggestion?

Upvotes: 0

Views: 1278

Answers (3)

PGallagher
PGallagher

Reputation: 3113

Try something like;

USE master
GO
ALTER DATABASE Test 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE Test MODIFY NAME = NewTest
GO
ALTER DATABASE NewTest
SET MULTI_USER 
GO

Be aware of the fact that this may not rename the physical file on the hard drive though.

Upvotes: 2

MikeB
MikeB

Reputation: 119

You have two options:

  1. Look for and kill all connections like OMG Priories suggest
  2. Open the database in Single Server Mode as described here: http://technet.microsoft.com/en-us/library/ms345598(v=sql.105).aspx

Upvotes: 0

mitchimus
mitchimus

Reputation: 828

There are a couple of things you need to investigate. The reason you're getting that error could be due to one or more of the following things:

  1. The account you're using does not have permission to run the command
  2. The DB is locked by another process/user
  3. The database contains a file group that is read-only

You can try and force single user mode to check 2.

ALTER DATABASE SINGLE_USER ROLLBACK IMMEDIATE.

That will kill any concurrent connections to the DB allowing you to rule out number two.

Upvotes: 0

Related Questions