The Multifunctioning DBA

Oct 12 2011   1:56PM GMT

Powershell Script to check if data and log files are growing by percent

Colin Smith Colin Smith Profile: Colin Smith

In my new shop we try to grow our data and log files by a specific amount and not by a percentage. Our team lead ran into a database that had not been set up properly and wanted someone to check all our databases to verify that they have been set up correctly. Since I am the new guy and still eager to impress I said I would take that task. So it took me about 30 minutes to complete the task but now it will only take me a few seconds to execute a powershell script.This script grabs a list of all our SQL Servers from a table in a database that we have the tracks this information. So the script will get the list and then loop through them all and report on the databases that have data and or log files set to grow by percent. here is the code.

#### 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 db -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)
{
$name = $database.name
## Check the log File
$logfiles = $database.logfiles
foreach($logfile in $logfiles)
{
$filename = $logfile.filename
$growth = $logfile.growth
$growthtype = $logfile.growthtype
if(($growthtype -eq “Percent”) -and ($name -ne “Master”) -and ($name -ne “Model”))
{
$Object = New-Object PSObject
$Object | add-member Noteproperty Database $name
$Object | add-member Noteproperty Growth $growth
$Object | add-member Noteproperty GrowthType $growthtype
$Object | add-member Noteproperty File $Filename
$Object | add-member Noteproperty Instnace $server

$output = $output + $object
#echo “$name $growth $growthtype $filename”
}
}
## Datafiles
$filegroups = $database.filegroups
foreach($group in $filegroups)
{
$files = $group.files
foreach($file in $files)
{
$filename = $file.filename
$type = $file.growthtype
$growth = $file.growth
if(($type -eq “Percent”) -and ($name -ne “Master”) -and ($name -ne “Model”))
{

$Object = New-Object PSObject

$Object | add-member Noteproperty Database $name
$Object | add-member Noteproperty Growth $growth
$Object | add-member Noteproperty GrowthType $type
$Object | add-member Noteproperty File $Filename
$Object | add-member Noteproperty Instnace $server
$output = $output + $object
}
}
}
}
}
$output | Out-GridView
$output.count
$output | Export-Csv c:\out.csv
##############################################################################
So you can see that n about 65 lines of code I was able to get a list of all of the databases that are configured incorrectly and it also reports on what file it is that is not set up correctly.
Hope this will help.

 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

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: