Day: December 16, 2011

Set Data and Log file default growth

Here’s a quick script to set the model database grown & autogrowth settings.  These settings are not a one-size fits all.  They’re just better defaults for my environment than the defaults (which are terrible for any environment, IMHO).  You must have SQLPSX installed for this to work.

$servers = @('Server1', 'Server2')

foreach($server in $servers)
{
	$logFiles = Get-SqlDatabase $server "model" | Get-SqlLogFile
	foreach($logFile in $logFiles)
	{
		$logFile.GrowthType = [Microsoft.SqlServer.Management.Smo.FileGrowthType]::KB
		$logFile.Growth = 51200;  #50mb
		$logFile.Alter();
	}
	
	$dataFiles = Get-SqlDatabase $server "model" | Get-SqlDataFile
	foreach($dataFile in $dataFiles)
	{
		$dataFile.GrowthType = [Microsoft.SqlServer.Management.Smo.FileGrowthType]::KB
		$dataFile.Growth = 102400;  #100mb  
		$dataFile.Alter();
	}
}