Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006

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


    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 ='',
    @subject ='SERVER X - Fresh Space Issue on D Drive',
    @message = 'Free space on D Drive
    has dropped below 2 gig'
    drop table #freespace


  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    Here'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
    	exec master.dbo.xp_smtp_sendmail
    		@SERVER = 'exchange.foobar.corp',
    		@FROM = @From,
    		@TO = N'',
    		@SUBJECT = @Subject,
    		@MESSAGE = @Message
    drop table #FreeSpace
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts