-- Stored Procedure: dbo.SpaceUsedByFileGroupLowFree
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SpaceUsedByFileGroupLowFree]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SpaceUsedByFileGroupLowFree]
GO




CREATE PROCEDURE [dbo].[SpaceUsedByFileGroupLowFree] 
 @recipients varchar(100),
 @server varchar(128) = null
AS
/*******************************************************************************************************
* admin.dbo.SpaceUsedByFileGroupLowFree 
* Creator: Bill Wunder      
* Date:    5-24-2002      
*
* Description:   send email of files where available bytes getting low
* Notes:         
*
* Usage:
      EXECUTE admin.dbo.SpaceUsedByFileGroupLowFree
  @recipients = 'bill.wunder@wallst.com'
*
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @subject varchar(100),
 @SQLStr nvarchar(4000)

---------------------------------------------
-- create temp tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON

---------------------------------------------
-- body of stored procedure
---------------------------------------------

if @server is null set @server = @@servername
set @subject = 'Database Data Files with low avaiable free space on ' + @server
set @SQLStr = 'set nocount on 
select cast([Database] as varchar(20)) [Database]
 , cast([FileName] as varchar(30)) [File]
 , [Size] [Size MB]
 , [SpaceAvailableInMB] [Available]
from admin.dbo.SpaceUsedHistory s
where Size > 16
and server = ''' + @server + '''
and RecCreatedDt = (select max(RecCreatedDt) 
   from admin.dbo.SpaceUsedHistory 
   where Server = s.Server
   and [Database] = s.[Database]
   and [FileName] = s.[FileName])
and SpaceAvailableInMB * 1024 < case
  when FileGrowthType = 0
   then .2*(FileGrowthInKB)
  when FileGrowthType = 1
   then .2*(SizeInKB*(FileGrowth*.01))
  end
and databaseproperty([Database], ''IsReadOnly'') = 0
and databaseproperty([Database], ''IsOffline'') = 0
and databaseproperty([Database], ''IsInStandBy'') = 0'

if exists (select 1
  from admin.dbo.SpaceUsedHistory s
  where Size > 16
  and server = @server
  and RecCreatedDt = (select max(RecCreatedDt) 
   from admin.dbo.SpaceUsedHistory 
   where Server = s.Server
   and [Database] = s.[Database]
   and [FileName] = s.[FileName])
  and SpaceAvailableInMB * 1024 < case
   when FileGrowthType = 0
    then .2*(FileGrowthInKB)
   when FileGrowthType = 1
    then .2*(SizeInKB*(FileGrowth*.01))
   end
and databaseproperty([Database], 'IsReadOnly') = 0
and databaseproperty([Database], 'IsOffline') = 0
and databaseproperty([Database], 'IsInStandBy') = 0)
 exec sysmon.dbo.safe_sendmail
  @recipients = @recipients
  , @subject = @subject
  , @query = @SQLStr

RETURN



GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO