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

    Unanswered: 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


    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here's what I use:
    Code:
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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