Database File(s) Growth Notification

,

This stored proc notify the user(s) if the data/log file grew. When you execute it for the first time it will get the data/log files sizes and inserts in a table. Next time when you run the script if will compare the current size with the previous files size. This script must need sqlmail configured on the server.

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

Rate

Share

Share

Rate