none
set autoclose property for all database

    Question

  • Can i set autoclose property as false for all databases together in a single query?
    Thursday, January 17, 2013 7:07 AM

Answers

  • You can use sp_msforeachdb for existing database. 

    Modify this property in Model database, so that when you create new database, it will be automatically reflected.

    sp_msforeachdb 'use ?;ALTER DATABASE ? SET AUTO_CLOSE OFF'


    Srinivasan

    Thursday, January 17, 2013 9:27 AM
  • here is the script

    EXECUTE sp_MSforeachdb
    '
    IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))
       EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')
    '


    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, January 17, 2013 9:59 AM

All replies

  • Hello,

    You could use the undocumented stored procedure sp_MSforeachdb, to execute a statement on all databases.


    Olaf Helper

    Blog Xing

    Thursday, January 17, 2013 7:25 AM
  • You can use sp_msforeachdb for existing database. 

    Modify this property in Model database, so that when you create new database, it will be automatically reflected.

    sp_msforeachdb 'use ?;ALTER DATABASE ? SET AUTO_CLOSE OFF'


    Srinivasan

    Thursday, January 17, 2013 9:27 AM
  • here is the script

    EXECUTE sp_MSforeachdb
    '
    IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))
       EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')
    '


    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, January 17, 2013 9:59 AM
  • As long as your database does not have space in between the database name then sp_MSforeachdb should work. also pls read

    http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx 

    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

    Thursday, January 17, 2013 10:06 AM
  • You could use power shell

    in SSMS , right click on database node in Object Explorer, Select menu item "Start Powershell"

    type in following in power shell window to set AutoClose on all databases

    dir | foreach {$_.AutoClose = $true; $_.Alter()}

    If you would prefer to set on specific database that starts with name "DB"

    dir | where-object {$_.Name.StartsWith("DB")} | foreach {$_.AutoClose = $true; $_.Alter()}


    Thanks, Sethu Srinivasan [MSFT] SQL Server http://blogs.msdn.com/sqlagent -------------------------------------------------------------------------------- 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.

    Thursday, January 17, 2013 7:59 PM
    Moderator