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

    Unanswered: Issue of Blank Emails when checking for Free Space on Disk of Servers

    Hello

    I have got a script which checks the percentage of free space on the drivers of the servers and mails the DBA when it falls below a certain percentage , which can be set according to our requirements.

    But I am getting blank emails even when , there is no issue of low free space.

    Please help me out.

    The code , 1st part of it is:

    use master
    go
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO
    create PROCEDURE usp_diskspace
    @Percentagefree int,
    @error2 varchar(8000) OUTPUT
    AS


    SET NOCOUNT ON
    DECLARE @hr int, @fso int, @drive char(1), @odrive int, @TotalSize varchar(20), @MB bigint , @COUNT int, @Maxcount int,@error varchar(700), @errordrive char(1),@errortotalspace varchar(20), @errorfreespace varchar(20), @free int, @date varchar(100), @query varchar(1300)
    SET @MB = 1048576
    set @date = convert(varchar(100), getdate(),109)
    set @error2=''
    select @query= 'master.dbo.xp_fixeddrives'
    set @date = convert(varchar(100), getdate(),109)
    set @error2=''
    select @query= 'master.dbo.xp_fixeddrives'


    CREATE TABLE #drives (id int identity(1,1),ServerName varchar(15),
    drive char(1) PRIMARY KEY,
    FreeSpace int NULL,
    TotalSize int NULL,
    FreespaceTimestamp DATETIME NULL)
    INSERT #drives(drive,FreeSpace)
    EXEC @query
    EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
    DECLARE dcur CURSOR LOCAL FAST_FORWARD
    FOR SELECT drive from #drives
    ORDER by drive
    OPEN dcur
    FETCH NEXT FROM dcur INTO @drive
    WHILE @@FETCH_STATUS=0
    BEGIN
    EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
    EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
    UPDATE #drives


    SET TotalSize=@TotalSize/@MB, ServerName = replace( @query , 'master.dbo.xp_fixeddrives',''), FreespaceTimestamp = (GETDATE())
    WHERE drive=@drive
    FETCH NEXT FROM dcur INTO @drive
    END
    CLOSE dcur
    DEALLOCATE dcur
    EXEC @hr=sp_OADestroy @fso
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
    set @maxcount =(select max(id) from #drives)
    set @count=1

    while @count <=@maxcount
    begin

    select @errortotalspace =convert(varchar(20),Totalsize), @errorfreespace =freespace, @free=CAST((FreeSpace/(TotalSize*1.0))*100.0 as int),@errordrive=Drive from #drives where id = @count

    if @free<@percentagefree
    begin
    set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB ate =' +@date
    set @error2=@error2+@error+char(13)

    end
    else
    begin
    set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB ate =' +@date
    end
    set @count=@count+1
    end

    DROP TABLE #drives
    set @date = convert(varchar(100), getdate(),109)

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO


    The 2nd step which sends the mail is :

    set nocount on
    declare @msg varchar(8000)
    declare @minimumspace int
    set @minimumspace = 10
    set @msg = 'Running out of Hard Disk space on the Server: '+@@servername
    exec usp_diskspace @minimumspace,@msg OUTPUT
    print @msg
    if @msg is not null


    EXEC master.dbo.xp_smtp_sendmail
    @FROM = N'fromaddress',
    @TO = N'toaddress',
    @server = N'smtp address',
    @subject = N'Free Space of Drivers Test sqlserver2000!',
    @type = N'text/html',
    @message = @msg



    How can I change to get the mail only when there is a free space issue.

    Thanks

  2. #2
    Join Date
    Sep 2006
    Posts
    21
    I have got the script corrected.

    Just I need to give :

    if len(@message) >1
    EXEC master.dbo.xp_smtp_sendmail
    @FROM = N'fromaddress',
    @TO = N'toaddress',
    @server = N'smtp address',
    @subject = N'Free Space of Drivers Test sqlserver2000!',
    @type = N'text/html',
    @message = @msg

    Thanks

Posting Permissions

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