Categories

SQL Server autoclose

Buck Woody blogged about SQL Server autoclose and how it should be set to being off  - http://blogs.msdn.com/buckwoody/archive/2009/06/24/sql-server-best-practices-autoclose-should-be-off.aspx 

I’m doing a lot with SQL Server and PowerShell at the moment and it started me thinking that I could easily set this property in PowerShell.

001
002
003
004
005
006
007
008
009
010
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server("SQL08")
$Server.databases | select Name, Autoclose
foreach ($db in $Server.Databases){
    if (!$db.IsSystemObject){
        $db.Autoclose = $false
        $db.Alter()
    }
}
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server("SQL08")
$Server.databases | select Name, Autoclose

 

Create a server object and display the databases, selecting name and the autoclose property.  Its boolean so we get true or false back.

We can iterate through the database collection. Test if the database is a system database and set autoclose to false (we don’t want autoclose to happen).  A call to Alter() saves the change.

Finally we can recreate the server object and  redisplay the autoclose property.

Technorati Tags: ,

Leave a Reply