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.