Solved

specify thresholds monitoring for MS SQL filegroups

Posted on 2013-06-02
10
199 Views
Last Modified: 2013-06-11
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
0
Comment
Question by:bibi92
  • 5
  • 3
  • 2
10 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 39214513
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39214514
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39214516
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 21

Expert Comment

by:oleggold
ID: 39214520
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39214521
#### 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
 

Author Comment

by:bibi92
ID: 39214643
Thanks a lot but I have to keep the orignal code and modified it.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 39221965
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
 

Author Comment

by:bibi92
ID: 39223395
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
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
ID: 39224019
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
 

Author Closing Comment

by:bibi92
ID: 39237256
Thanks
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question