I am trying to set an alert if the REMAINING AVAILABLE space of my SQL mdf data FILE is less than 2000 MB. See below:
$SQLInstances = "A","B","C"
$SQLQuery = " USE DB
SELECT CONVERT(DECIMAL(10, 2), CAST(CAST(10240 - size/128.0 AS NVARCHAR(50)) + size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS NVARCHAR(50))) AS ActualSpaceAvailableMB
FROM sys.database_files
WHERE name = 'DB'"
$login = "XYZ"
$password = "123" | Convertto-SecureString -AsPlainText -Force
$creds = New-Object System.Management.Automation.Pscredential -Argumentlist $login,$password
$SMTPServer = "10.0.0.0.0.0" # This is your SMTP Server
$to = "[email protected]"# This is the recipient smtp address 1
$from = "[email protected]" # This will be the sender´s address
foreach ($SQLInstance in $SQLInstances) {
$output = Invoke-Sqlcmd -ServerInstance $SQLInstance -Database DB -Query $SQLQuery |
Select-Object -ExpandProperty ActualSpaceAvailableMB #-QueryTimeout 1200 -Verbose -ErrorAction Stop 4>&1
if ($output -lt 2000 ) {
Send-MailMessage -SmtpServer $SMTPServer -Credential $creds -To $to -From $from -Subject " Alert at $SQLInstance" -Body "free space is $output MB. Please investigate!!"
} else {
Write-Output "Actual size is not less than 2 GB"
}
}
Now the problem sometimes the alert gives incorrect results i.e. even if the value in the $output variable is greater than 2000 MB an email is still being send. I want it to send an email ONLY if the value in the $output is less than 2000. Any ideas?
