The Multifunctioning DBA

Oct 14 2011   8:25AM GMT

Powershell Script to change data and log files that are growing by percent



Posted by: Colin Smith
Tags:
Powershell
SMO
SQL Server
This is the next step after finding all the files that are growing by percent and not by a fixed value. Below is the modified script to make the changes that I needed.

#### Check Log and Data Growth for all User Databases. If they are not set up correctly then change them to the correct setting.
$servers = invoke-sqlcmd -serverinstance server -database Database -query “select InstanceName, Port from instance where IsActive = 1″
$output = @()
foreach($instance in $servers)
{
$name = $instance.instancename
$port = $instance.port
$server = “$name,$port”
#echo “`n###########################

######”
#$server

$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $server

$databases = $s.databases
#$databases | get-member | where {$_.membertype -eq “Property”}
foreach($database in $databases)
{
$dbsize = $database.size
$name = $database.name
## Check the log File
$logfiles = $database.logfiles
foreach($logfile in $logfiles)
{
$filename = $logfile.filename
$growth = $logfile.growth
$growthtype = $logfile.growthtype
$size = $logfile.size
if(($growthtype -eq “Percent”) -and ($name -ne “Master”) -and ($name -ne “Model”))
{
$logfile.growth = 65536
$logfile.growthtype = “kb”
$logfile.alter()

}
}
## Datafiles
$filegroups = $database.filegroups
foreach($group in $filegroups)
{
$files = $group.files
foreach($file in $files)
{
$filename = $file.filename
$type = $file.growthtype
$growth = $file.growth
$size = $file.size
if(($type -eq “Percent”) -and ($name -ne “Master”) -and ($name -ne “Model”))
{
if($dbsize -lt 10240)
{
$file.growth = 51200
$file.growthtype = “kb”
$file.alter()
}

if(($dbsize -gt 20480) -and ($dbsize -lt 51200))
{
$file.growth = 307200
$file.growthtype = “kb”
$file.alter()
}

if(($dbsize -gt 51200) -and ($dbsize -lt 102400))
{
$file.growth = 512000
$file.growthtype = “kb”
$file.alter()
}

if($dbsize -gt 102400)
{
$file.growth = 1048576
$file.growthtype = “kb”
$file.alter()
}

}
}
}
}
}

##############################################################################
So you can see that this script is a bit more code, but I was able to make all the changes that I needed to all the log and all the data files and I was able to have the datafile growth depend on the current size of the database.
Hope this will help.

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • dana803
    how very cool. Exactly what I need.  Since I'm still new to Powershell though, I have a question about how I get it to find the servers and db's.  We have multiple servers with multiple db's. I see the first line for $servers.  Do I need to specify the server and database names in place of the server and database words on that line? Or, is there a way to specify each server name and let it find the databases as it looks like what this is trying to do?
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: