locked
SQL Denali Contained database RRS feed

  • Question

  • Hi,

    Environment :Windows 2008 R2, SQl Denali CTP 3

    Issue :

    This is an existing database.

    I enabled containment at the server level

    When i go to enable containment ( partial ) at the database level, it gives me an error. There is no entry in the error log either.

    Alter Database Failed because a lock could be placed on the database 'mydb'. ALTER DATABASE statement failed.

    Note : there is no other connection active on the database. I even restarted the SQL instance.

    However, i am able to create a contained database from scratch without any problem.

    Any ideas

    

    thanks

    venk

    Wednesday, November 2, 2011 3:28 AM

Answers

  • Hi venk,

    Does the code work on the link provided by Alberto? Could you please post the statement you used? You may have a try to set the database to SINGLE_USER before ALTER DATABASE:
    USE [master]
    
    GO
    
    ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    
    GO
    

    And go back to MULTI_USER after ALTER:
    USE [master]
    
    GO
    
    ALTER DATABASE <dbname> SET MULTI_USER 
    
    GO
    


    Best Regards,
    Stephanie Lv

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Stephanie Lv Thursday, November 3, 2011 5:11 AM
    Thursday, November 3, 2011 3:38 AM
  • sorry i was a bot late in getting back

    i managed to get it working. There was one connection alive to the database which i had overlooked. once i killed the connection, it worked.

     

    thanks for all yr help

     

    regards

    venk

    • Marked as answer by Stephanie Lv Thursday, November 3, 2011 5:11 AM
    Thursday, November 3, 2011 3:50 AM

All replies

  • Hello,

    Please collapse that database on Management Studio, and enable containment using T-SQL like Aaron Bertrand explained in the following post:

    http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/16/sql-server-v-next-denali-contained-databases.aspx  (Section “Turning containment on”)

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, November 2, 2011 4:45 AM
  • Hi venk,

    Does the code work on the link provided by Alberto? Could you please post the statement you used? You may have a try to set the database to SINGLE_USER before ALTER DATABASE:
    USE [master]
    
    GO
    
    ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    
    GO
    

    And go back to MULTI_USER after ALTER:
    USE [master]
    
    GO
    
    ALTER DATABASE <dbname> SET MULTI_USER 
    
    GO
    


    Best Regards,
    Stephanie Lv

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Stephanie Lv Thursday, November 3, 2011 5:11 AM
    Thursday, November 3, 2011 3:38 AM
  • sorry i was a bot late in getting back

    i managed to get it working. There was one connection alive to the database which i had overlooked. once i killed the connection, it worked.

     

    thanks for all yr help

     

    regards

    venk

    • Marked as answer by Stephanie Lv Thursday, November 3, 2011 5:11 AM
    Thursday, November 3, 2011 3:50 AM