We've started using Windows mount points on our Enterprise SQL Server cluster because we've run out of drive letters. Unfortunately, our Orion monitoring software does not support monitoring mount point disk usage without the purchase of an additional license. Rather than go down that path, I scrubbed the internet for a Powershell script that could do the monitoring for me. Unfortunately, I've lost the source - but it wasn't copyrighted so I shouldn't be sued for re-posting the solution.
The script is running locally on one of three servers in the Windows cluster. It queries cluster services to find the host name of each server in the cluster. It then does a check for all disk drives on each host and sends an email if their usage is above a set threshold. This is scheduled to run once a day for me using task scheduler, but you can run it as you like. This script also requires that you are using SQL Server 2008 R2 or higher. There are likely other requirements specific to your environment in order to get this to work, you'll have to work your way through those issues.
###############################################################################
# Check the percentage of free space, including mount points
###############################################################################
# For use in sending alerts via SQL Server Database Mail and may be scheduled
# via a SQL Server Agent job. Create a new Agent job and creat a new step, set
# the Type as PowerShell, edit the code below to put in your server name(s),
# Database Mail profile name, and recipient email address(es), optionally
# change the warning threshold, then paste the code into the Command pane and
# create a schedule.
###############################################################################
# Server names (not SQL Server instance names) must be in single quotes and
# separated by commas. If the SQL Server Agent service account does not have
# admin permissions on remote servers, you must assign Remote Execute
# permissions to the Agent account on each remote server, and the Agent account
# must be a domain account. To assign Remote Execute, run wmimgmt.msc,
# right-click/Properties, select the Security tab, expand the Root node, select
# the CIMV2 node, click the Security button, add the Agent account and scroll
# down to find and check the box for the "Remote Enable" permission.
# 01.17.14 - K. Bumber - I've populated the list of servernames automatically via get-clusternode below.
# Set threshold percentage for low disk capacity warnings.
$Threshold = .1
# Set Email to alert with disk is below threshold
$To = 'dbaemail@domain.com'
# Threshold value must be between 0 and 1.
# (E.g. ".1" will produce warnings when free space is below 10%.
# Set values for units of measure
[string]$UnitOfMeasure = '1GB'
$UnitOfMeasureTerm = "GB"
# Use an empty string for bytes or KB/MB/GB/TB/PB
# (KB-PB are constants in PowerShell)
function sendMail($from, $to, $subject, $body){
#Write-Host "Sending Email"
#SMTP server name
$smtpServer = "smtp.server.com"
#Creating a Mail object
$msg = new-object Net.Mail.MailMessage
#Creating SMTP server object
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
#Email structure
$msg.From = "$from"
#$msg.ReplyTo = "replyto@xxxx.com"
$msg.To.Add("$to")
$msg.subject = "$subject"
$msg.body = "$body"
#Sending email
$smtp.Send($msg)
}
# List the servers you want to check for low disk capacities
Import-Module FailoverClusters
$ServerCol = get-clusternode
ForEach ($ServerName in $ServerCol)
{
$Volumes = Get-WmiObject -namespace "root/cimv2" -computername $ServerName.name -query "SELECT Name, Capacity, FreeSpace, DriveType FROM Win32_Volume WHERE DriveType = 3"
ForEach ($Volume in $Volumes)
{
[string]$DriveType = Switch($Volume.DriveType)
{
0{'Unknown'}
1{'No Root Directory'}
2{'Removable Disk'}
3{'Local Disk'}
4{'Network Drive'}
5{'Compact Disk'}
6{'RAM Disk'}
default {'Unknown'}
}
#If($DriveType -eq 'Unknown'){BREAK}
#If $Volume.Name does not start with a capital letter and : then BREAK to the next step in ForEach.
[string]$JustName = "{0}" -f$Volume.Name
#write-output $JustName
If("$JustName" -match "^[A-Z]:*")
{
[string]$Drive = "Drive: {0}" -f$Volume.Name
[string]$Capacity = "Capacity: {0} {1}" -f[System.Math]::Round(($Volume.Capacity / $UnitOfMeasure),0), $UnitOfMeasureTerm
[string]$FreeSpace = "Free Space: {0} {1}" -f[System.Math]::Round(($Volume.FreeSpace / $UnitOfMeasure),0), $UnitOfMeasureTerm
[string]$PercentFree = "Percent Free Space: " + [System.Math]::Round(($Volume.FreeSpace / $Volume.Capacity), 2)*100 + "%"
[string]$DriveType = "DriveType: " + $Volume.DriveType
# Send an email alert via Database Mail if a disk is below the warning threshold
If ($Volume.FreeSpace / $Volume.Capacity -lt $Threshold)
{
[string]$Subject = "WARNING: Low Disk Space on $ServerName"
[string]$Body = "WARNING: Low free space on $ServerName `n
$Drive `n
$Capacity `n
$FreeSpace `n
$PercentFree"
#edit the line below to figure out where the email came from
[string]$From = "$ServerName@yourdomain.com"
sendMail $From $To $Subject $Body
}
###########################################################################
# If you'd like to get a disk capacity report interactively, comment-out
# the If statement above and comment-in the following If statement:
<#
If ($Volume.FreeSpace / $Volume.Capacity -lt .9)
{
Write-Output "WARNING: Disk capacity is less than 90%"
Write-Output "Server: $ServerName"
Write-Output $Drive
Write-Output $Capacity
Write-Output $FreeSpace
Write-Output $PercentFree
Write-Output $DriveType
}
Else
{
Write-Output "Server: $ServerName"
Write-Output $Drive
Write-Output $Capacity
Write-Output $FreeSpace
Write-Output $PercentFree
Write-Output $DriveType
}
#>
}
}
}
No comments:
Post a Comment