Checking for free disk space and getting mail when it falls below a certain limit

Hello

I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.

I would like to know how I can change it , as a percentage calculation.

For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.

The script I have is :

declare @.MB_Free int

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace

Thanks

Hi Vaddi -

Why not use the Alerts feature in Performance Monitor? The Logical Disk Performance Object has a Counter for % Free Space and you can select which drive letter you'd like to monitor. Once the limit is reached, you can have it email you using a WSH script.

HTH...

checking for free disk space and getting mail , when falls below a certain limit

Hello

I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.

I would like to know how I can change it , as a percentage calculation.

For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.

The script I have is :

declare @.MB_Free int

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace

ThanksHere's what I use:
set nocount on

declare @.MB_Threshold int
set @.MB_Threshold = 102400
declare @.From varchar(500)
declare @.Subject varchar(500)
declare @.Message varchar(500)

create table #FreeSpace(Drive char(1), MB_Free int)

insert into #FreeSpace exec master..xp_fixeddrives

select @.Message = isnull(@.Message + ', ', 'The following drives have dropped below ' + cast(@.MB_Threshold as varchar(10)) + ' MB free space: ') + Drive
from #FreeSpace
where MB_Free < @.MB_Threshold

set @.From = @.@.ServerName
set @.Subject = 'Drive space warning!'

if len(@.Message) > 0
begin
exec master.dbo.xp_smtp_sendmail
@.SERVER = 'exchange.foobar.corp',
@.FROM = @.From,
@.TO = N'blindman@.dbforums.com',
@.SUBJECT = @.Subject,
@.MESSAGE = @.Message

end

drop table #FreeSpace
go