Technical Article

Files MaxSize

,

A Procedure send Alert by E-Mail about all
files with option "Restrict File Growth" if
available space less then 10%.

/******************************************************************************************** 
**Name: p_file_maxsize.
**Desc: report about files, when size is critical
**            ( < 10% available space)  . 
** 
**Called by: 
**            execute p_file_maxsize 
**              
**Input:           Output: table master..file_critsize,E-Mail
**      -------------    -------------------------------------------
**Author : Mushkatin Vadim.DBA,Israel. vadimm@bezeq.com
**Created: 31/7/2002. 
********************************************************************************************/set quoted_identifier off
set nocount on
go

Create  proc  p_file_maxsize as

declare     @subject             varchar(50),
            @count               tinyint,
            @dbname              varchar(100),
            @filename            varchar(200),
            @size                float,
            @max_size            int,
            @percent_used_space  float      
     
set @subject = @@servername + ' File Critical Size '

if object_id('master..file_critsize') is not null
   drop table master..file_critsize
Create table master..file_critsize (
   [db_name]               varchar (100) ,
   [file_name]             varchar (200) ,
   file_size               int ,
   max_size                int ,
   [percent_used_space]    int 
)   
              
declare c cursor for
   select d.name [db_name], f.name [file_name],size,maxsize  
        from master..sysaltfiles f,master..sysdatabases d
        where f.dbid = d.dbid and d.name  not in ('Northwind','pubs')
              and maxsize <> -1 
 
open c 
fetch c into @dbname,@filename,@size,@max_size 
while @@fetch_status = 0 
begin 
       set @percent_used_space = (@size / @max_size) * 100
       if  @percent_used_space > 89 
       begin
           insert into  master..file_critsize 
                        ([db_name],[file_name],file_size,max_size,percent_used_space)
                  values(@dbname,@filename,@size,@max_size,@percent_used_space)
       end     

       fetch c into @dbname,@filename,@size,@max_size 
end 
close c 
deallocate c

select @count = count(*) 
from master..file_critsize
if @count > 0
begin 
   SET ANSI_NULLS     ON
   SET ANSI_WARNINGS  ON
   Exec master..xp_sendmail @recipients ='Your@E-MailAddress',
      @query = 'select substring([db_name],1,20) as dbname , 
                       percent_used_space,substring([file_name],1,20) as file_name 
                from master..file_critsize ', 
      @subject = @subject ,
      @message = 'The output of p_file_maxsize:',
      @attach_results = 'TRUE', @width = 250
end

Go

Read 1,675 times
(6 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating