Monitor Sql Server disk space

/**********************************************
Example: EXEC [DBA_DiskSpaceMntr]
@mailto = ‘dba mail’,
@CDrivethreshold = 10240,
@OtherDrivethreshold = 20480
**********************************************/

CREATE PROCEDURE [dbo].[DBA_DiskSpaceMntr]
@mailto nvarchar(4000),
@CDrivethreshold INT,
@DDrivethreshold INT,
@YDrivethreshold INT,
@OtherDrivethreshold INT
AS
BEGIN
declare @count int;
declare @DiskFreeSpace int;
declare @tempfspace int;
declare @tempdrive char(1);
declare @mailbody nvarchar(4000);
declare @MailSubject nvarchar(1000);
declare @AlertMessage nvarchar(4000);
declare @altflag bit;
declare @sub nvarchar(4000);
declare @cmd nvarchar(4000);
set @count = 0;
SET @mailbody = ”;
SET @cmd = ”;
set nocount on
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
SELECT @DiskFreeSpace = fspace FROM #driveinfo where drive in (‘C’)
IF @DiskFreeSpace < @CDrivethreshold
Begin
SET @MailSubject = ‘Drive C: free space is low on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar)
SET @mailbody = ‘Drive C: on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar) + ‘ has only ‘ + CAST(@DiskFreeSpace AS VARCHAR) + ‘ MB left. Please free up space on this drive. ‘
–select * FROM #driveinfo where drive in (‘L’)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLDBA_Support’,
@recipients= @mailto,
@subject = @MailSubject,
@body = @mailbody,
–@file_attachments = @logfile,
@body_format = ‘HTML’
End
SELECT @DiskFreeSpace = fspace FROM #driveinfo where drive in (‘D’)
IF @DiskFreeSpace < @DDrivethreshold
Begin
SET @MailSubject = ‘Drive D: free space is low on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar)
SET @mailbody = ‘Drive D: on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar) + ‘ has only ‘ + CAST(@DiskFreeSpace AS VARCHAR) + ‘ MB left. Please free up space on this drive. ‘
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLDBA_Support’,
@recipients= @mailto,
@subject = @MailSubject,
@body = @mailbody,
–@file_attachments = @logfile,
@body_format = ‘HTML’
End
SELECT @DiskFreeSpace = fspace FROM #driveinfo where drive in (‘Y’)
IF @DiskFreeSpace < @YDrivethreshold
Begin
SET @MailSubject = ‘Drive Y: free space is low on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar)
SET @mailbody = ‘Drive Y: on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar) + ‘ has only ‘ + CAST(@DiskFreeSpace AS VARCHAR) + ‘ MB left. Please free up space on this drive. ‘
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘profile_name’,
@recipients= @mailto,
@subject = @MailSubject,
@body = @mailbody,
–@file_attachments = @logfile,
@body_format = ‘HTML’
End
set @mailbody=”;
while (select count(*) from #driveinfo ) >= @count
begin
set @tempfspace = (select fspace from #driveinfo where id = @count and drive not in (‘C’,’Q’,’D’,’Y’))
set @tempdrive = (select drive from #driveinfo where id = @count and drive not in (‘C’,’Q’,’D’,’Y’))
if @tempfspace < @OtherDrivethreshold
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 (@altflag = 1)
BEGIN
SET @sub = ‘Monitor Space on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar)
set @mailbody = ‘The below drives on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar) + ‘ have low disk space then threshold limit ‘ + CAST(@OtherDrivethreshold as VARCHAR(10)) +’ Please free up the space in below specified drives <p>’ + @mailbody
–print ‘Space on ‘ + @tempdrive + ‘: is very low: ‘ + str(@tempfspace)+ ‘MB’
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Profile name’,
@recipients= @mailto,
@subject = @sub,
@body = @mailbody,
–@file_attachments = @logfile,
@body_format = ‘HTML’
END
drop table #driveinfo
set nocount off
END

Leave a Reply

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