Database File(s) Growth Notification

Use msdb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_dbinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_dbinfo]
GO

CREATE TABLE [dbo].[tbl_dbinfo] (
[Database Name] [varchar] (10)  NULL ,
[DB GROWTH] [varchar] (6)  NULL 
) ON [PRIMARY]
GO

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

CREATE TABLE [dbo].[tbl_DBSizes] (
[dbid] [smallint] NOT NULL ,
[fileid] [smallint] NOT NULL ,
[filesize] [int] NOT NULL 
) ON [PRIMARY]
GO


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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_GetDBFileSizes AS

SET NOCOUNT ON

DECLARE DB_Names CURSOR FOR SELECT name FROM master..sysdatabases ORDER BY dbid
DECLARE @db_name varchar(255)
DECLARE @SQL nVARCHAR(1000)
DECLARE @fileid smallint
DECLARE @filesize int

-- Truncate the table 
DELETE FROM msdb..tbl_DBSizes


OPEN DB_Names
FETCH NEXT FROM DB_Names INTO @db_name

WHILE ( @@FETCH_STATUS = 0 )
BEGIN 

SElect @SQL = 'DECLARE File_IDs CURSOR FOR SELECT 
fileid, size FROM  '+ @db_name +' ..sysfiles '

Exec sp_executesql @SQL


OPEN File_IDs 
FETCH NEXT FROM File_IDs INTO @fileid, @filesize

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO tbl_DBSizes 
SELECT db_id( @db_name), @fileid, @filesize
FETCH NEXT FROM File_IDs INTO @fileid, @filesize
END
CLOSE File_IDs
DEALLOCATE File_IDs

FETCH NEXT FROM DB_Names INTO @db_name
END

Close DB_Names
DEALLOCATE DB_Names

SET NOCOUNT OFF


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_CheckFileGrowth 
AS

DECLARE @fileid smallint
DECLARE @New_filesize int
DECLARE @Old_filesize int
DECLARE @Filesize_diff int
DECLARE @db_name varchar(255)
DECLARE @size int
DECLARE  @SQL nvarchar(1000)
DECLARE @Filesize_diff1 varchar(6)


DECLARE New_DB_Names CURSOR FOR SELECT name FROM master..sysdatabases ORDER BY dbid

OPEN New_DB_Names

FETCH NEXT FROM New_DB_Names INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN 
SELECT @SQL = ' DECLARE New_File_IDs CURSOR FOR
SELECT fileid, size FROM ' + @db_name + '..sysfiles'

Exec sp_executesql @SQL

OPEN New_File_IDs 
FETCH NEXT FROM New_File_IDs INTO @fileid, @New_filesize

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Old_filesize = 
(SELECT filesize FROM msdb..tbl_DBSizes
WHERE dbid = db_id(@db_name) 
AND fileid = @fileid)

SELECT @Filesize_diff = CAST(((@New_filesize - @Old_filesize) * 8) / 1024 As Varchar)

IF ( @Old_filesize <> @New_filesize) And (@Filesize_diff > 0)

BEGIN

UPDATE tbl_DBSizes 
SET filesize = (filesize + (@New_filesize - @old_filesize))
WHERE dbid = db_id(@db_name)  AND fileid = @fileid


Select @Filesize_diff1 = CONVERT(VARCHAR(5),@Filesize_diff) + ' MB'

Insert into tbl_dbinfo values ( @db_name, @Filesize_diff1)


EXEC master..xp_sendmail 
     @recipients = '<Mail_ID'
 ,@message = ' Database File(s) Growth occurred'
  ,@subject = 'Database File growth Notification'
  ,@query   =  'SElect [Database Name],[DB GROWTH]  from msdb..tbl_dbinfo'
  ,@attach_results = 'TRUE'
  ,@no_header = 'false'
   ,@width = 300
Delete from tbl_dbinfo

END


FETCH NEXT FROM New_File_IDs INTO @fileid, @New_filesize
END
CLOSE New_File_IDs
DEALLOCATE New_File_IDs


FETCH NEXT FROM New_DB_Names INTO @db_name
END
Close New_DB_Names
DEALLOCATE New_DB_Names

Exec usp_GetDBFileSizes

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO