Files MaxSize

/******************************************************************************************** 
**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