SQL Server Free Disk Space Monitor Alerts – SQL Script

Hello,

I had a SQL Server fee disk space monitor job in my environment which throws an alert when the free space on any drive drops below a threshold free space. But it did not have the information to help us directly attack the server and the right drive and which folder and file exactly has grown. So I have decided to have the information handy. When I can receive the alert then why not in detail? So here is the stored procedure which 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 crunch. Also in the mail it provides the information of which drive is running low on space.

SET ANSI_NULLS ON
 
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
/*----------------------------------------------------------------------------------------------------------
Version 1: Manohar Punna 3/1/2011
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 = 'mymailid@mymail.com',
                          @threshold = 10240,
                          @logfile = 'L:\DBA\DiskSpaceLog.txt'
------------------------------------------------------------------------------------------------------------*/
 
/****** Object: Stored Procedure dbo.DiskSpaceMntr******/
 
CREATE PROCEDURE [dbo].[DiskSpaceMntr]
@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
 
--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)
 
--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 @altflag = 1;
SET @mailbody = @mailbody + '<p>Drive ' + CAST(@tempdrive AS NVARCHAR(10)) + ' has ' + CAST(@tempfspace AS NVARCHAR(10)) + ' MB free</br>'
 
SET @cmd = 'dir /s /-c ' + @tempdrive + ':\ > ' + @logfile
 
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 = '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'
 
END
 
drop table #driveinfo
 
set nocount off
 
END

If you have any issues with the code please feel free to get back to me.

Happy Monitoring.

Regards

Manohar Punna

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook