----------------------------------------------------------------------

--  This SP will interrogate the DASD table for databases with low free
--  space. Acceptable input structure is as follows:
--
--  exec sp_Monitor_Email_Low_DB_FreeSpace @limitpercent, @exceptDBName,
--       @exceptDBlimitper, @exceptDBName2, @exceptDBlimitper2 
--
--  exec sp_Monitor_Email_Low_DB_FreeSpace 0.1
--  exec sp_Monitor_Email_Low_DB_FreeSpace 0.1, PROD, 0.02
--  exec sp_Monitor_Email_Low_DB_FreeSpace 0.1, PROD, 0.02, DBCC4PROD, 0.02
-- 
--  DEFAULT for @limitpercent      = 0.1
--  DEFAULT for @exceptDBName*     = _NONE_
--  DEFAULT for @exceptDBlimitper* = 0.02
-- 
--  Analysis of DB 'autogrowth/file size restriction' vs sysfiles maxsize,
--  growth values is as follows:
--
--   Database File Charactersitics          sysfiles.maxsize sysfiles.growth
--    Autogrow      by 10%  Unrestrict          -1                   10
--    NO Autogrow   by 10%  Unrestrict          -1                    0
--    Autogrow      by 10%  Restrict 100MB   12800 8KB pages         10
--    NO Autogrow   by 10%  Restrict 100MB   12800 8KB pages          0

--   sysfiles.maxsize of '-1' means only restricted by disk space, if 
--   sysfile.growth is not 0. The 'by 10%' and even 'Restrict 100MB' is only 
--   used if sysfiles.growth not 0.
--
--   THUS, if sysfiles.growth is '0', there can be NO file or database growth.
--   THIS, if sysfiles.growth is > 0, and sysfiles.maxsize = '-1' - then only 
--         restriction is disk space. In these cases the free space warning 
--         would come from the job 'HH - DISK DRIVE SPACE INFO'

USE msdb
GO
CREATE  procedure sp_Monitor_Email_Low_DB_FreeSpace
@limitpercent      float     = 0.1,
@exceptDBName      char(20)  = '_NONE_',
@exceptDBlimitper  float     = 0.02,
@exceptDBName2     char(20)  = '_NONE_',
@exceptDBlimitper2 float     = 0.02
as

declare @dbname           char(20)
declare @freeDB           float
declare @totalDB          float
declare @limitpercentHLD  float
declare @db_maxsize       int
declare @db_growth        int
declare @subject          varchar(120)
declare @message          varchar(120)

select @limitpercentHLD = @limitpercent
-----------------------------------------------------------------------
--  This section is for Database free space analysis on databases
--  that had free space data collected and placed into DASD today.
-----------------------------------------------------------------------
declare ms_crs_c1 cursor for
    select distinct db_name from msdb..DASD
             where substring(createDTM,1,08) = convert(varchar(08),getdate(),112)

---------------------------------------------------------------------------------
--  This section processes database names in this cursor. Variables @freeDB and
--  @totalDB are initalized to '2' and '1'.
---------------------------------------------------------------------------------
open ms_crs_c1
fetch ms_crs_c1 into @dbname
while @@fetch_status >= 0
begin
select @freeDB=2, @totalDB=1

----------------------------------------------------------------------
--  THis SQL allows for 2 exception databases for special limitpercent.
--  Here we check if we have those database names, if so we set the exception
--  percent - else we reset to the normal limitpercent.
----------------------------------------------------------------------
select @limitpercent = @limitpercentHLD
If (select @dbname) = @exceptDBName
 begin
  select @limitpercent = @exceptDBlimitper
 end

If (select @dbname) = @exceptDBName2
 begin
  select @limitpercent = @exceptDBlimitper2
 end

----------------------------------------------------------------------------------
-- free_DB_space and @total_DB_space are self defining. db_maxsize indicates the
-- growth characteristics of this database. '-1' and db will grow to limits of
-- disk drive its files are on, '0' means no autogrowth and any other positive
-- number states the limits of database file growth in KB. Here we skip databases
-- with '-1' growth characteristics, warnings will come from 'HH - Disk Drive Space
-- INFO'.
-----------------------------------------------------------------------------------
select @freeDB=free_DB_space, @totalDB=total_DB_space, @db_maxsize=db_maxsize,
       @db_growth=db_growth
    from  msdb..DASD
  where db_name = @dbname
    and substring(createDTM,1,08)= convert(varchar(08),getdate(),112)

---------------------------------------------------------------------------------
--  This section processes database with NO file growth allowed
---------------------------------------------------------------------------------
IF (select @db_growth) = 0
 BEGIN
  IF (select (@freeDB/@totalDB)) < @limitpercent
    BEGIN
      select @subject = 'DATABASE ' + @dbname + ', ' + substring(@@servername,1,20) +
                   ' IS LOW ON FREE SPACE'
      select @message = @dbname + ', ' + substring(@@servername,1,20) +
                   ' HAS LESS THAN ' + convert(char(4),@limitpercent) + ' FREE SPACE'
      execute master..xp_sendmail
       	   @recipients = 'SQLmail',
       	   @subject    = @subject,
       	   @message    = @message
    END
 END

---------------------------------------------------------------------------------
--  This section processes database with file growth allowed, to a max size . The  
--  existing free space is added to the allowable file growth and divided by the 
--  resulting Total to get the maximum potential database growth.
---------------------------------------------------------------------------------
IF (select @db_growth) > 0
 BEGIN
  IF (select @db_maxsize) > 0
   BEGIN
    IF (select ((@db_maxsize - @totalDB) + @freeDB)/@totalDB) < @limitpercent
     BEGIN
      select @subject = 'DATABASE ' + @dbname + ', ' + substring(@@servername,1,20) +
                        ' IS LOW ON FREE SPACE'
      select @message = @dbname + ', ' + substring(@@servername,1,20) + 
                        ' HAS LESS THAN ' + convert(char(4),@limitpercent) + ' GROWTH SPACE'
      execute master..xp_sendmail
              @recipients = 'SQLmail',
              @subject    = @subject,
              @message    = @message
     END
   END
 END



fetch ms_crs_c1 into @dbname
end
deallocate ms_crs_c1

---------------------------------------------------------------------------------
--  This section deletes DASD entries that are over 1 year old and NOT Created
--  on a FRIDAY.
---------------------------------------------------------------------------------
declare @thedate varchar(8)
select  @thedate = convert(char(08),(DATEADD(yyyy,-1,getdate())),112)
delete  msdb..DASD 
 where substring(createDTM,1,8) < @thedate
   and datepart(dw,convert(varchar(08),substring(createDTM,1,8),112)) <> 6


---------------------------------------------------------------------------------
--  This section looks for new databases on this machine, that are not in
--  msdb..DASD
---------------------------------------------------------------------------------
IF (select count(s.name) from master..sysdatabases s
    where convert(char(11),s.crdate,112) = convert(char(11),(dateadd(dd,-1,getdate())),112)
      and s.name <> 'tempdb') > 0
    BEGIN
      select @subject = 'New Database(s) Were Created on ' + substring(@@servername,1,20) + 
                        'Yesterday, First Alert'
      select @message = 'New Database(s) Were Created on ' + substring(@@servername,1,20) + 
                        'Yesterday, First Alert'
      execute master..xp_sendmail
       	   @recipients = 'SQLmail',
       	   @subject    = @subject,
       	   @message    = @message
    END

IF (select count(s.name) from master..sysdatabases s
    where convert(char(11),s.crdate,112) = convert(char(11),(dateadd(dd,-4,getdate())),112)
      and s.name <> 'tempdb') > 0
    BEGIN
      select @subject = 'New Database(s) Were Created on ' + substring(@@servername,1,20) + 
                        '4 Days Ago, Final Alert'
      select @message = 'New Database(s) Were Created on ' + substring(@@servername,1,20) + 
                        '4 Days Ago, Final Alert'
      execute master..xp_sendmail
       	   @recipients = 'SQLmail',
       	   @subject    = @subject,
       	   @message    = @message
    END
go