Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Low Disk Space Alert for SQL Server


By:   |   Read Comments (18)   |   Related Tips: More > 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="DBServer@domain.com"
$emailTo="email2@domain.com"

# 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:

Email Alert

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

Email Alert

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.


Last Update:






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

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, June 29, 2016 - 10:59:58 AM - Kevin Back To Top

 Nevermind, I found the issue.  It was a misconfiguration of my Net.Mail.SmtpClient

 

Thanks for the script!  It is working great now.

Kevin

 


Wednesday, June 29, 2016 - 10:24:13 AM - Kevin Back To Top

Svetlana,
When I run the script, I recieve the following error:

A job step received an error at line 28 in a PowerShell script. The corresponding line is '    $smtp.Send('. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "Send" with "4" argument(s): "Failure sending mail."  '.  Process Exit Code -1.  The step failed.

.

 

I am not really all that versed in PowerShell, so I am a bit stuck.
Do you have any suggestions?

Kevin

 


Tuesday, January 26, 2016 - 3:59:26 PM - Svetlana Golovko Back To Top

 
Hi,

The script already sends alerts only when available space is below the specified threshold. You may need to modify the thresholds to your own values:

 

$driveCthreshold=10
$threshold=60

 

$driveCthreshold  - the threshold for the C: drive.

$threshold - for the rest of the drives.

 

Thanks,

Svetlana

 


Monday, January 25, 2016 - 2:12:20 PM - Awhyoh T. Back To Top

Hello

 

Huge thanks for the script. I would like to know how to go about updating script so that an alert email is sent only when available space is below the specified threshold. Thaks again for the script.

 


Saturday, October 03, 2015 - 7:00:15 PM - Svetlana Back To Top

Hi Dennis,

 

I am pretty sure you are looking at the job's properties.

You need to create the new job step and then you will see the options that are displayed on the screenshot.

 

Thanks,

Svetlana


Thursday, October 01, 2015 - 7:40:46 AM - Dennis S. Back To Top

Hi, I thank you for your tip, but when I try to add the job, my options are not the same as your article.  Instead of "Type" I have "Category".  ??  I have SQL Server 2008 R2 Standard.  I can't find any place to enter the PowerShell code...

 


Friday, September 18, 2015 - 2:59:03 PM - Chad Churchwell Back To Top

adinarayana -

If you have a central management server (CMS) set up and all your servers registered you could easily have an SSIS package that queries all the registered servers from the CMS, and dynamically connects to each one and runs the script.  You would need to save this off into a central repository database, then lay an SSRS report on top of that table for enterprise wide results reporting.

This is one way, there are several others but I think the process is common, just implementation is different.  Your server list could be in a txt file, database table, etc.  I like CMS.

Here is a link to get you started with CMS

https://msdn.microsoft.com/en-us/library/bb934126(v=sql.110).aspx

Here is the query I use to get all my registered servers after CMS is set up

SELECT
--DISTINCT groups.name AS 'Server Group Name',
svr.server_name AS 'Server Name'
FROM msdb.dbo.sysmanagement_shared_server_groups_internal groups
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr
ON groups.server_group_id = svr.server_group_id
WHERE groups.name <> 'Offline'

 


Friday, September 18, 2015 - 7:52:36 AM - adinarayana Back To Top

Hi ,

 

can i scheduled this script for centralized sql server to get the drive space report to accross all the server at a time


Monday, September 14, 2015 - 11:31:32 AM - Svetlana Golovko Back To Top

Craig,

This will not work with mount points.


Monday, September 14, 2015 - 11:27:26 AM - Svetlana Golovko Back To Top

Hi Pratheesh,

 

I am not familiar with "Professional" edition. I am not sure if you got the right edition's name.

You can run this statement to find to find out what edition you have: SELECT SERVERPROPERTY('Edition')

But regardless the edition if you can create a job then you should be able to specify a type of the Job Step (not the Job, the Step itself).

 

 

 

 

 

 


Sunday, September 13, 2015 - 3:22:32 AM - Pratheesh Back To Top

Thank you for your tip on creating power shell script to validate disk space, but I am facing an issue, in my SQL 2012 Professinal edition, I am not getting the dropdown for Type, Instead I have the drop down for category. Is there any seperate configuration for this? I


Thursday, September 10, 2015 - 3:42:20 PM - Craig Silvis Back To Top

For more granular control over all drives I did this:

$arrayDriveLetterThresholds  =  ("A:",10),("B:",10),("C:",10),("D:",10),("E:",10),("F:",10);

$arrayDriveLetterThresholds += ,("G:",10),("H:",10),("I:",10),("J:",10),("K:",10),("L:",10);

$arrayDriveLetterThresholds += ,("M:",10),("N:",10),("O:",10),("P:",10),("Q:",10),("R:",10);

$arrayDriveLetterThresholds += ,("S:",10),("T:",10),("U:",10),("V:",10),("W:",10),("X:",10);

$arrayDriveLetterThresholds += ,("Y:",10),("Z:",10);

 

# Get SQL Server hostname

$hostname=Get-WMIObject Win32_ComputerSystem | Select-Object -ExpandProperty name;

 

$emailFrom="$hostname@example.com";

$emailServer = "smtp.example.com";

$emailTo="superDBA@example.com";

 

# 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";

 

ForEach ($Result In $Results)

{

    $drive = $Result.DriveLetter;

    $space = $Result.FreeSpace/1GB;

    foreach($valuePair in $arrayDriveLetterThresholds)

    {

        if(($drive -eq $valuePair[0]) -and ($space -lt $valuePair[1]))

        {

            $currentThreshold = $valuePair[1];

            $emailSubject = "Disk $drive on $hostname has less than $currentThreshold GB of free space left!";

            $emailBody = "`r`n" + $emailSubject + "`r`nCurrent amount of free space on drive $drive = " + [math]::truncate($space).ToString() + " GB.";

            Send-MailMessage -From $emailFrom -To $emailTo -Subject $emailSubject -Body $emailBody -Priority High -SmtpServer $emailServer;

        }; 

    };

};

 

Thursday, September 10, 2015 - 8:07:45 AM - Svetlana Back To Top

Murugan

You may need to create a proxy for the PowerShell and run the job as this proxy account (not as SQL Agent). The login used for the proxy must have permissions to run WMI queries on the server.


Thursday, September 10, 2015 - 1:54:40 AM - Murugan Back To Top

I'm getting the below error while running the job;

 

Message

Executed as user: ---\SYSTEM. The job script encountered the following errors. These errors did not stop the script:  A job step received an error at line 14 in a PowerShell script. The corresponding line is '$hostname=Get-WMIObject Win32_ComputerSystem | Select-Object -ExpandProperty name'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot expand property "name" because it has nothing to expand.  '  A job step received an error at line 17 in a PowerShell script. The corresponding line is '$Results = Get-WmiObject -Class Win32_Volume -Filter "SystemVolume='False' AND DriveType=3"|'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Invalid query   '  A job step received an error at line 25 in a PowerShell script. The corresponding line is '    $thresh = if($drive -eq 'C:'){$driveCthreshold} else {$threshold}'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'if' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again.  '.  Process Exit Code -1.  The step failed.


Wednesday, September 09, 2015 - 7:46:51 PM - Svetlana Back To Top

Hi Chad,

 

Yes, it works with mount points, but you will need to replace

$drive = $Result.DriveLetter

 

with

$drive = $Result.Name

or

$drive = $Result.Caption

to have the correct drive name in the e-mail subject


Wednesday, September 09, 2015 - 5:07:03 PM - Bruce Back To Top

Victor - Yes, you can use the task scheduler to run a version of this. You would need to specify the -ComputerName for Get-WMIObject. You could put all your servers in an active directory group and then pull them from AD or you can create a table or text file and read from them and iterate through them. Just beware, you'll need to create an excpetion in your firewall to allow powershell to send email. In my current environment our hardware firewall won't permit powershell to send emails, so you might have to either use the sql agent, smo + sp_send_dbmail, or an email client to get around those rules (you can create outlook objects in powershell).

Chad - Get-WMIObject win32_Volume pulls the volume information (disks and mount points), Get-WMIObject win32_LocalDisk will only get disks.


Wednesday, September 09, 2015 - 12:33:48 PM - Victor Sturm Back To Top

Would it be possible to run this PowerShell script outside of SQL?

I am not only a SQL admin, but our network and server admin in general. I think this script would have uses outside of just SQL particularly in a Virtual environment.

 

Thanks.

Victor


Wednesday, September 09, 2015 - 10:06:56 AM - Chad Churchwell Back To Top

Very nice solution, quick question, does it work with Mount Points?


Learn more about SQL Server tools