Browse FAQs

SQL Server Disk Space Monitor Alerts

Published date: 10 September 2019

Need to automate the process to monitor the total disk space and available disk space and send an alert at a
predetermined threshold in order to prevent running out of space and the problems that would be associated.

Product Line
StruxureWare Power Monitoring 7.0.x
Power Monitoring Expert 7.x, 8.x, 9.x
ION Enterprise 6.0.x

SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017

Proactive automated system maintenance best practice.

*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

The following stored procedure sends an alert when any drive size drops below a set threshold and also
includes an attachment of all the folders and files inside each folder and its size in Bytes in the drive where
we have the space deficiency. Also in the mail it provides the information of which drive is running low on space.

To implement this:
1) highlight and copy the script below or download the attached file to a desired location
2) launch SQL Server Management Studio either:
- open a new Query window and paste the text copied from below
- select File -> Open,  then locate the downloaded file to open
3) execute the script within the Query window to create the stored procedure
4) create a new scheduled SQL job to execute the stored procedure with your individual parameters as per the Example contained in the script.
** further information on how to create and schedule a SQL job can be found within your SQL Server documentation or obtained using the Help within SSMS.

Desc: Send Disk space alert with free space details in each drive
input: @mailto - recepients list
         @mailProfile - DBMail Profile.
         @threshold - Threshold Free space in MB below which you need the alert to be sent.
         @logfile - Log file to hold the file size details and send it as attachment.
output: Send Mail
Warnings: None.
Example: EXEC [DiskSpaceMntr]
                          @mailProfile = 'SQL_Profile',
                          @mailto = '',
                          @threshold = 10240,
                          @logfile = 'D:\DBA\DiskSpaceLog.txt'
/****** Object: Stored Procedure dbo.DiskSpaceMntr******/
CREATE PROCEDURE [dbo].[DiskSpaceMntr]
@mailProfile nvarchar(500),
@mailto nvarchar(4000),
@threshold INT,
@logfile nvarchar(4000)
declare @count int;
declare @tempfspace int;
declare @tempdrive char(1);
declare @mailbody nvarchar(4000);
declare @altflag bit;
declare @sub nvarchar(4000);
declare @cmd nvarchar(4000);
SET @count = 0;
SET @mailbody = '';
SET @cmd = '';
--Create temp table to hold drive free space info
IF EXISTS(select * from sys.sysobjects where id = object_id('#driveinfo'))
drop table #driveinfo
create table #driveinfo(id int identity(1,1),drive char(1), fspace int)
insert into #driveinfo EXEC master..xp_fixeddrives
--Loop through each drive to check for drive threshold
while (select count(*) from #driveinfo) >= @count
set @tempfspace = (select fspace from #driveinfo where id = @count)
set @tempdrive = (select drive from #driveinfo where id = @count)
--If free space is lower than threshold appends details to mail body and dumps the file size details into the logfile.
if @tempfspace < @threshold
SET @altflag = 1;
SET @mailbody = @mailbody + '
Drive ' + CAST(@tempdrive AS NVARCHAR(10)) + ' has ' + CAST(@tempfspace AS NVARCHAR(10)) + ' MB free
SET @cmd = 'dir /s /-c ' + @tempdrive + ':\ > ' + @logfile
EXEC xp_cmdshell @cmd
set @count = @count + 1
--If at least one drive is below threshold level sends the mail with attachment
IF (@altflag = 1)
SET @sub = 'Monitor Space on ' + CAST(@@SERVERNAME AS NVARCHAR(30))
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @mailProfile,
@recipients= @mailto,
@subject = @sub,
@body = @mailbody,
@file_attachments = @logfile,
@body_format = 'HTML'
drop table #driveinfo
set nocount off


Was this helpful?

What can we do to improve the information ?

Can't find what you are looking for?

Reach out to our customer care team to receive information on technical support, assistance for complaints and more.