specify thresholds monitoring for MS SQL filegroups

Hello

            I try to specify thresholds monitoring for MS SQL filegroups.
            With the code below, the last specific threshold is applied to a database filegrou is not in the list $ LIST_SPEC_DB

            Monitoring.txt contains the following lines for exclude MS SQL Server filegroups :
            MASTER-PRIMARY:DB:0%:50%
            TEMPDB-PRIMARY:DB:2M:8M


            $_WARN_THRES="30%"
            $_PANIC_THRES="90%"

            $DBSPACE contains all MS SQL Filegroups

		$CHCK_SUP_ENV = (Test-Path $MONITORING -PathType Leaf)

		If (($CHCK_SUP_ENV)) {
		$Global:LIST_SPEC_DB=get-content $MONITORING | where-object {$_ -match(":DB:")}  -ErrorAction silentlycontinue
	
		
		if ($LIST_SPEC_DB -ne $null) {
		foreach ($DBS in $LIST_SPEC_DB) {
		$array_str = $DBS.split(":")
		$SPEC_DBSPACE = $array_str[0]
		$SPEC_WARN_SPRV = $array_str[2]
		$SPEC_PANIC_SPRV  = $array_str[3] 
					
		if ($DBSPACE -eq $SPEC_DBSPACE -and $SPEC_WARN_SPRV -match "%") {
		$_WARN_THRES=$SPEC_WARN_SPRV.replace("%","")
  
  		$_PANIC_THRES=$SPEC_PANIC_SPRV.replace("%","")
		
		$_UNIT="%"
		
		}
	
		elseif ($DBSPACE -eq $SPEC_DBSPACE -and $SPEC_WARN_SPRV -match "M") {
		$_WARN_THRES=$SPEC_WARN_SPRV.replace("M","")
  
  		$_PANIC_THRES=$SPEC_PANIC_SPRV.replace("M","")
		
		$_UNIT="M"
		
	
		
		}
		}
		}
		}
		

		
		elseif ($LIST_SPEC_DBS -eq $null -or $DBSPACE -ne $SPEC_DBSPACE ) {
		
		foreach ($DBS in $DBSPACE) {
		
  		$_WARN_THRES=$SEUIL_WARN_SPRV.replace("%","")
  
  		$_PANIC_THRES=$SEUIL_PANIC_SPRV.replace("%","")
		
		$_UNIT="%"

		}
		}

Open in new window

           How can I resolve this problem?

            Thanks
bibi92Asked:
Who is Participating?
 
SubsunCommented:
Check this article from Scripting Guy! Blog and see if it helps.. It explains how to obtain SQL Server database sizes using PowerShell..

http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/02/use-powershell-to-obtain-sql-server-database-sizes.aspx
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
oleggoldCommented:
#### 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()
}

}
}
}
}
}

Open in new window

0
 
bibi92Author Commented:
Thanks a lot but I have to keep the orignal code and modified it.
0
 
SubsunCommented:
Are you still looking for help on this?
If yes..

I can see you are reading the thresholds from a text file.. but I don't see any code which reads the current size of the database or database volume which you need to validate against the threshold.. Please correct me If I am wrong..

Once you get those values then you can use the comparison operators -lt -le or -gt -ge depends on the threshold settings..
http://ss64.com/ps/syntax-compare.html
0
 
bibi92Author Commented:
Yes, I'm still looking for help. Yes, I'm readind the thresholds from a text file but I search write code which reads the current size of the database or database volume which you need to validate against the threshold.
Thanks
0
 
bibi92Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.