If you have been reading my blog, you know by now that I like to get alerts for failures or warnings. Who wants to go and check things on individual servers, am I right? Also, I do not like to use monitoring software – if you are a DBA you possibly know what I mean.
I use a dedicated instance of SQL Server to run my monitoring scripts etc. I schedule a SQL Agent job on this monitoring instance named Alert_LowDiskSpace which runs following PowerShell script every hour (feel free to change frequency depending on you needs) during week days between 6 AM to 11 PM only. I run different schedule for weekends if required, or keep it same as weekdays – depends on your needs.
The script basically reads through a text file with list of servers that need to be monitored, checks free space available and sends alerts if thresholds are reached ( I used different thresholds for system and non-system drives).
I am not pasting a sample email alert here, but rest assured the email is going to look beautiful and will be meaningful – just try it.
POWERSHELL SCRIPT:
######################### DEFINING VARIABLES ################################
$TO = “DBA Team<xyzabc@abc.com>”
$From = “SQL Server DB Alert<dbamonitoring@abc.com>”
$SMTPServer = “xyzsmtpserver.abc.com”
$PercentFreeThreshold = 10
$PercentFreeThresholdForC = 5
######################### DEFINING FUNCTIONS ################################
# (FuncMail) Function to send email
function FuncMail {
param($To, $From, $Subject, $Body, $smtpServer)
$msg = new-object Net.Mail.MailMessage #creating a mail object
$smtp = new-object Net.Mail.SmtpClient($smtpServer) #creating SMTP server object
#Email Structure
$msg.From = $From
$msg.To.Add($To)
$msg.Subject = $Subject
$msg.IsBodyHtml = 1
$msg.Body = $Body
#$mailmessage.Priority = [system.net.mail.mailpriority]::high
$smtp.Send($msg)
}
######################### Script Logic ################################
foreach ($ServerName in get-content “\\abcServerName\D$\DBA_DoNotDelete\PowerShellScripts\Config\Servers.txt”)
{
Get-WMIObject Win32_LogicalDisk -filter “DriveType=3” -computer $ServerName | where-object {$_.deviceID -ne “C:”}`
| Select SystemName,DeviceID,VolumeName,@{Name=”GBTotal”; Expression={“{0:N1}” -f($_.size/1gb)}},@{Name=”GBFree”;Expression={“{0:N1}” -f($_.freespace/1gb)}},@{Name=”PercentFree”;Expression={“{0:N2}” -f(($_.freespace/$_.Size)*100)}}, @{Name=”PercentFull”;Expression={“{0:N2}” -f((100)-(($_.freespace/$_.Size)*100))}} `
| Foreach {
if ([decimal]$_.PercentFree -lt [decimal]$PercentFreeThreshold -and [decimal]$_.GBTotal -gt 0)
{
$subject = “:( !!! ” + $_.DeviceID +” Drive – ” + $_.PercentFull + “% Full on server $servername”
$body = “The server ” + $_.SystemName + ” has only ” + $_.GBFree + ” GB free space remaining on ” + $_.DeviceID + ” Drive out of total ” + $_.GBTotal + ” GB available space. Please attend to the issue immediately.”
FuncMail -To $To -From $From -Subject $subject -smtpServer $smtpserver -body $body
}
}
}
foreach ($ServerName in get-content “\\abcServerName\D$\DBA_DoNotDelete\PowerShellScripts\Config\Servers.txt”)
{
Get-WMIObject Win32_LogicalDisk -filter “DriveType=3” -computer $ServerName | where-object {$_.deviceID -eq “C:”}`
| Select SystemName,DeviceID,VolumeName,@{Name=”GBTotal”; Expression={“{0:N1}” -f($_.size/1gb)}},@{Name=”GBFree”;Expression={“{0:N1}” -f($_.freespace/1gb)}},@{Name=”PercentFree”;Expression={“{0:N2}” -f(($_.freespace/$_.Size)*100)}}, @{Name=”PercentFull”;Expression={“{0:N2}” -f((100)-(($_.freespace/$_.Size)*100))}} `
| Foreach {
if ([decimal]$_.PercentFree -lt [decimal]$PercentFreeThresholdForC -and [decimal]$_.GBTotal -gt 0)
{
$subject = “:( !!! ” + $_.DeviceID +” Drive – ” + $_.PercentFull + “% Full on server $servername”
$body = “The server ” + $_.SystemName + ” has only ” + $_.GBFree + ” GB free space remaining on ” + $_.DeviceID + ” Drive out of total ” + $_.GBTotal + ” GB available space. Please attend to the issue immediately.”
FuncMail -To $To -From $From -Subject $subject -smtpServer $smtpserver -body $body
}
}
}
ONESQL 