A common task for many database administrators (DBAs) is to set up alert emails to notify themselves when free disk space falls below a certain threshold (e.g. 10%). Before SQL Server ran on Windows clusters that included mount-points, there were a number of methods for checking free disk space, but most didn't report on the free space in mount point based disks. The easiest way to check free space on mount points is via Windows Management Instrumentation (WMI), and the easiest way to use WMI and create an alert is with PowerShell.
Attached to this article is a WMI-PowerShell script that checks disk free space, including mount points. It's written to be used in a SQL Server Agent job, and send an email alert via SQL Server's Database Mail. You can run it against any number of servers from one script, and you'll need to replace several items in the script: Server name(s), the Database Mail profile name, the recipient email address(es), and optionally change the warning threshold. It works like any other Agent job except you choose a job step Type of "PowerShell". Detailed notes on how to tweak and use the script are included as in-line notes in the script file.
If you're not familiar with PowerShell and you'd like to view the code in color-coded formating, use the PowerShell ISE (Start menu/Accessories/Windows PowerShell/Windows PowerShell ISE). (ISE stands for Integrated Scripting Environment.)
Here's a security-related wrinkle to WMI calls via PowerShell that's run by SQL Server Agent... If the service account that SQL Server Agent runs under has local admin permissions/privileges and you only run the script against the local server, you probably won't have any access-denied problems. Under any other scenarios, such as the Agent having limited local permissions or you target the script against remote servers and the Agent service account doesn't have admin permissions on those servers, you'll have to grant a WMI "Remote Execute" persmission to the Agent account on each server. To assign Remote Execute, run wmimgmt.msc from the Start menu's Run box or from a command prompt, then right-click on Properties, then select the Security tab, then expand the Root node, then select the CIMV2 node, then click the Security button, then add the Agent account and scroll down to find and check the box for the "Remote Enable" permission. Simple, right? You were probably going to guess that anyway. ;)
Microsoft
