Low Disk Space Alert for SQL Server

By: Svetlana Golovko   |   Updated: 2015-09-09   |   Comments (32)   |   Related: > Monitoring

Problem

SQL Server Database Administrators do not always have tools in place for monitoring free disk space. One option are these PowerShell scripts for monitoring disk space, but I am an overloaded SQL Server DBA and need a low disk space alert for SQL Server to automate the monitoring and notification process. Can you provide any recommendations?

Solution

In this tip we will provide steps for automated email notifications when disk space is lower than a specified threshold. To accomplish this we will setup a SQL Server Agent Job that executes the previously mentioned PowerShell script. The PowerShell script will check the disk space based on the threshold parameters passed to the code. Then, if the disk space is lower than the threshold, the script will send a formatted email to the address you specify.

Keep in mind SQL Server should be able to send emails in order for the script to work properly and parameters from this script need to be updated to your specific environment.

PowerShell script

# 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=10
$threshold=60


# Replace settings below with your e-mails
$emailFrom="[email protected]"
$emailTo="[email protected]"

# 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")
}

The script above checks free space based on two different thresholds - one for the system drive (C:) and another one for the rest of the drives. We are using 10GB for the C: drive and 60GB for the other drives for this demo. Setup the thresholds based on your database growth rate or remaining disk space.

PowerShell Formatting and Conversion Options

In our script we used byte conversion to check the disk space in gigabytes versus bytes when compared against the threshold (which is in gigabytes):

Where-Object {......
$_.FreeSpace/1GB –lt  $threshold ......}

Also, we used the "Truncate" method from the [math] class to strip off the decimal points. Note, that we wanted to display the result in the e-mail body in megabytes:

[math]::truncate($space/1MB)

Then, we used .Net formatting to display number with comma for thousands: "16,837 MB".

"{0:N0}" -f [math]::truncate($space/1MB)

Read more about "-f" format operator here.

Read more about PowerShell formatting here.

Create SQL Server Agent job

Now we will create an SQL Server Agent Job that will be executing this PowerShell script. Select "PowerShell" as a job's step type:

Here is a sample e-mail that will be sent if any of the drives have less free disk space than the specified thresholds:

Read SQL Server Agent tips to find more details on how to setup the jobs.

Next Steps

• Make sure you monitor your SQL Servers disk space either with third party monitoring tools or with custom scripts.
• Find more PowerShell tips here.
• Read other tips about checking disk space here and here.
• Read this tip about determining free disk space with T-SQL.
• Read other tips about SQL Server Monitoring.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips