I found this gem while troubleshooting a SQL Server with a backup drive that filled up often. It uses the WMI provider will alert when ANY drive on the server goes below 10GB. You can change the value ‘10000000000’ below to adjust this. You’ll also want to change your operator from ‘Support’ to your operator name.
Note that this requires Database Mail to be set up with an operator and valid email. Should work for SQL 2005 thru 2012 as is, mine was set up on SQL 2005.
The ability to use WMI Provider in SQL Alerts seems very useful, I have seen uses for WMI SQL Alerts, but they involved more code than just creating the alert. If you’re interested in exploring the metrics WMI exposes to you, check out the WMI Tester included in Windows. There’s also more information in WMI at the Microsoft site.
Been using it for a while and thought to share it. I couldn’t locate the blog where I found it originally. If that’s you, let me know so I can thank you. If you have any other useful SQL Alerts that take advantage of WMI, please let me know as well.
EXEC msdb.dbo.sp_update_alert @name=N'Disk Less that 10GB',
@wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA "Win32_LogicalDisk" AND TargetInstance.FreeSpace < 10000000000', @job_id=N'00000000-0000-0000-0000-000000000000' GO --Replace Support with your Operator Name EXEC msdb.dbo.sp_update_notification @alert_name=N'Less that 10GB', @operator_name=N'Support', @notification_method = 1 GO