SQL Server autoclose

Richard's profileRichard Siddaway's BlogPhotosBlogLists

< Previous Next >

June 25

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: PowerShell,SQL Server

1:05 PM | Blog it | PowerShell and SQL Server

Comments

Richard Siddaway has turned off comments on this page.

Trackbacks

The trackback URL for this entry is:

http://richardsiddaway.spaces.live.com/blog/cns!43CFA46A74CF3E96!2430.trak

Weblogs that reference this entry

None