SQL SERVER – Disk Space Monitoring – Detecting Low Disk Space on Server

CREATE PROCEDURE [CSMSDVLP].[DiskSpaceMonitor]
@mailProfile nvarchar(500),
@mailto nvarchar(4000),
@threshold INT,
@logfile nvarchar(4000)
AS

BEGIN

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 = '';

SET NOCOUNT ON

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

 SET @mailbody ='Dear Team, </br>'

--Loop through each drive to check for drive threshold
while (select count(*) from #driveinfo) >= @count
begin

set @tempfspace = (select fspace from #driveinfo where id = @count)
set @tempdrive = (select drive from #driveinfo where id = @count)

 --SET @threshold=@threshold/1024

 --select @tempfspace
 --select @tempdrive
 --select @threshold

--If free space is lower than threshold appends details to mail body and dumps the file size details into the logfile.
if @tempfspace < @threshold
BEGIN
SET @tempfspace=@tempfspace/1024
SET @altflag = 1;

SET @mailbody = @mailbody + '<p>Drive ' + CAST(@tempdrive AS NVARCHAR(10)) + ' has ' + CAST(@tempfspace AS NVARCHAR(10)) + ' GB Free in __ Server</br>'

SET @cmd = 'dir /s /-c ' + @tempdrive + ':\ > ' + @logfile
-- select @mailbody
--EXEC xp_cmdshell @cmd

END

set @count = @count + 1

end

--If atleast one drive is below threshold level sends the mail with attachment
IF (@altflag = 1)
BEGIN

SET @sub = 'Free Space is lessthan 25GB  in _______ Server ' + CAST(@@SERVERNAME AS NVARCHAR(30))
SET @mailbody = @mailbody+ '</br> Thanks & Regards, </br> Srinivas Lakkapally'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SendEmailSqlProfile',
@recipients= 'test@gmail.com',

@subject = @sub,
@body = @mailbody,
--@file_attachments = @logfile,
@body_format = 'HTML'

END
--select * from #driveinfo
drop table #driveinfo

set nocount off

END