none
Error: the database could not be exclusively locked to perform the operation in sql server 2008 ?

    Question

  • I am trying to rename the database but i am getting below exception while doing it-->

    Error: the database could not be exclusively locked to perform the operation.(Microsoft Sql Server,Error 5030)

    Thanks.

    Tuesday, October 23, 2012 9:08 AM

Answers

  • Thats because someone else is accessing the database.. Put the database into single user mode the rename it.

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

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Andrew Bainbridge Tuesday, October 23, 2012 9:15 AM
    • Marked as answer by Maggy111 Tuesday, October 23, 2012 9:22 AM
    Tuesday, October 23, 2012 9:11 AM

All replies

  • Thats because someone else is accessing the database.. Put the database into single user mode the rename it.

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

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Andrew Bainbridge Tuesday, October 23, 2012 9:15 AM
    • Marked as answer by Maggy111 Tuesday, October 23, 2012 9:22 AM
    Tuesday, October 23, 2012 9:11 AM
  • Hi,

    Sounds like you have active connections to the database.  You can verify this by running sp_who2, or through Activity Monitor and look for your database.

    You could set the database to single user, i.e:

    ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    then do your sp_renamedb 'myolddbname', 'mynewdbname' or;

    ALTER DATABASE mydb MODIFY NAME = mynewname



    Thanks, Andrew


    Tuesday, October 23, 2012 9:15 AM
  • as mentioned earlier by both these users just wait for all the connections to go and then try- it will succed

    or else if it is dev\Uat environment & you can kill the conenctions - make the changes (use this as last option)

    i was just wondering if you are connected to the same DB? If yes change your context to master(means connect to master DB ) and then issue this command.


    Sarabpreet Singh Anand

    Blog , Personal website

    This posting is provided , "AS IS" with no warranties, and confers no rights.

    Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, October 23, 2012 9:23 AM
  • you forgot a step: setting the db back to multi-user afterwards.
    Wednesday, July 31, 2013 5:32 PM
  • you forgot a step, telling me what the command to set back to multi-user is.
    Friday, August 16, 2013 12:25 AM
  • ALTER DATABASE DB_NAME SET MULTI_USER WITH ROLLBACK IMMEDIATE;
    • Proposed as answer by Suresh7358 Tuesday, April 1, 2014 6:05 AM
    Tuesday, April 1, 2014 6:05 AM