info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Migrations, Replication & Cloud



Powershell - Disk Space Alert
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2019-10-07







SQL Job - Disk Space Low - Alert


# Replace "smtp.domain.com" with your mail server name
$smtp=new-object Net.Mail.SmtpClient("smtp.domain.com")

# Set thresholds (in gigabytes) for C: drive and for the remaining drives
$driveCthreshold=25
$threshold=25

# Replace settings below with your e-mails
$emailFrom="sqlserver@domain.com"
$emailTo="alerts@domain.com"

# Get SQL Server hostname
$hostname=Get-WMIObject Win32_ComputerSystem | Select-Object -ExpandProperty name

# Get all drives with free space less than a threshold. Exclude System Volumes
$Results = Get-WmiObject -Class Win32_Volume -Filter "SystemVolume='False' AND DriveType=3"|
Where-Object {($_.FreeSpace/1GB –lt $driveCthreshold –and $_.DriveLetter -eq "C:")`
–or ($_.FreeSpace/1GB –lt $threshold –and $_.DriveLetter -ne "C:" )}

ForEach ($Result In $Results)
{
$drive = $Result.DriveLetter
$space = $Result.FreeSpace
$thresh = if($drive -eq 'C:'){$driveCthreshold} else {$threshold}

# Send e-mail if the free space is less than threshold parameter
$smtp.Send(
$emailFrom,
$emailTo,
# E-mail subject
"Disk $drive on $hostname has less than $thresh GB of free space left ",
# E-mail body
("{0:N0}" -f [math]::truncate($space/1MB))+" MB")
}