SQL Server Disk Space Monitor Alerts

Issue
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
ION EEM

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

Cause
Proactive automated system maintenance best practice.

Resolution
*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: 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.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*----------------------------------------------------------------------------------------------------------
Desc: Send Disk space alert with free space details in each drive
------------------------------------------------------------------------------------------------------------
input: @mailto - recipients 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 an attachment.
output: Send Mail
Warnings: None.
------------------------------------------------------------------------------------------------------------
Example: EXEC [DiskSpaceMntr]
@mailProfile = 'SQL_Profile',
@mailto = 'mymailid@mymail.com',
@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)
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 + '
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
END
set @count = @count + 1
end
--If at least 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

Publicado para:Schneider Electric España