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();
	}
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: