space check

,

1- create the procedure

2- create a job to run it like every 2 horus.

3- create operator to send a mail to.

4- create alert to repond for error (@string,16,1)

create procedure harddisk_check_space
as
begin
    declare @mytable table (drive varchar(10),sp int) 
    declare @c int
    declare @notc int

    insert into @mytable exec master..xp_fixeddrives

    select @c=sp from @mytable
    where upper(drive)='C'
    select @notc=sum(sp) from @mytable
    where upper(drive)<>'C'

    set @c=@c/1000
    set @notc=@notc/1000

    if(@c<20 or @notc <3)
        begin
        declare @string varchar(8000)
        declare @convertedC varchar(200)
        declare @convertedNotC varchar(200)
        
        set @convertedC=convert(varchar(20),@c)
        set @convertedNotC=convert(varchar(20),@notc)
    
        set @string='there is not enough space left on the server, '+@convertedC+' GB left on C: and '+@convertedNotC+' GB left on the other drives'
        raiserror(@string,16,1) with log
        
        end
end

Rate

1.5 (4)

Share

Share

Rate

1.5 (4)