Alert On Low Disk Space, Including Mount Points


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. 😉

Check disk free space including mount points.ps1

Comments (6)
  1. lauraldo2 says:

    Thank you so much for this tip and the script.  I find it a little more user-friendly to display the data in a table format.  See below for script that does that.  It takes the server name as a parameter, so it is to be used from the command line, but it can be easily modified to be run from a SQL Server Agent job.

    Thanks again,

    Aldo Gonzalez

    Param($ServerName)

    $freespaceprct = @{ expression={ [float] $_.freespace / $_.capacity * 100} ; label='Freespace %';format="{0:N1}";width=12;alignment='right'}

    $sizeMB = @{ expression={ [float] $_.capacity / 1024 /1024}; label='Size (MB)';format="{0:N1}";width=10;alignment='right'}

    $FreespaceMB = @{ expression={ [float] $_.freespace / 1024 /1024}; label='Freespace (MB)';format="{0:N1}";width=15;alignment='right'}

    $deviceid = @{ expression={ $_.Name }; label='Drive/MountPoint' }

    $Volumes = Get-WmiObject -namespace "root/cimv2" -computername $ServerName -query "SELECT Name, Capacity, FreeSpace FROM Win32_Volume WHERE DriveType

    = 2 OR DriveType = 3"

    $Volumes | sort-object -property freespace | ft $deviceid, $sizeMB, $FreespaceMB, $freespaceprct -autosize

  2. Mayor Funk says:

    Beautiful John, Thanks very much for posting it

  3. CH SURESH says:

    HOW TO SOLVE MOUNT POINT ALERT FOR REAL TIME

  4. Fredrik says:

    For those of you that use SCOM to monitor, as we do, it’s OK to have a Mount point, f.ex. to extend the disk size With "folders". But add a drive letter to the disk, in addition to use the mount point path you already have, and SCOM will pick it up like
    a normal disk and monitor it. It worked for us when we tested.

  5. kumar says:

    This is an AWESOME script!

Comments are closed.

Skip to main content