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 TwitterFollow me on FaceBook

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

6 Comments on “SQL Server Free Disk Space Monitor Alerts – SQL Script”

  1. Hi Satnam,

    Updated the code with comments. Please let me know if needed any further changes. Thanks for pointing out.

    Regards,

    Manu

  2. Nice attempt.. At first I would suggest to use powershell for such alerts…. Then..replace while loop with a single Query.. Loops aren’t for T-SQL … And lastly.. Xp_cmdshell is not a good practice on production server ..

  3. Ahmad said: “Xp_cmdshell is not a good practice on production server ”

    It’s really amazing to me how that myth got started. Using XP_CmdShell properly in SQL Server is NOT a bad practice. It’s NOT even a security risk.

    What IS a bad practice and what IS a security risk is giving anyone or anything other than DBA’s SA privs or giving someone the privs to run XP_CmdShell directly without being an SA.

    –Jeff Moden

Leave a Reply

Your email address will not be published. Required fields are marked *