Log and report on table growth for trending

,

This script contains all that is required to set up regular or adhoc monitoring of table growth in your environments, so you can see growth hotspots and do some trend analysis. The script above is pretty self explanatory... you just need to follow the below steps highlighted in the script in the order they are listed. 1 -- Create the stats collection table 2 -- Collect the table size statistics 3 -- HTML Reporting

--############################################################################################################################
--
 --This script is being offered for public use and as such is being offered as untested and unverified.
 --Please use this script at your own risk, as I take NO responsibility for it's use elsewhere in environments 
 --that are NOT under my control. 
 --Redistribution for sale of [dba_table_growth_report], in whole or in part, is prohibited! 
 
 --Always ensure that you run such scripts in test prior to production and perform due diligence as to whether they meet yours, 
 --or your company needs!
--
--############################################################################################################################
--
-- Author:			Haden Kingsland
--
-- Date:			3rd May 2016
--
-- Description :	To report on database table growth over a rolling period,
--					based upon the data held in the following <your admin database>
--					table... dba.DB_TableSpace.
--					The data for these tables being populated daily by the following
--					SQL Agent job...
--
--					Monitor Database Table Size
--
--					...which both run at 07:45 every morning.
--
-- Modification History
-- ####################
--
---#######################################
-- 1 -- Create the stats collection table
---##################################################################################################################
--
-- you will need to create this table before you create the procedure!!!!
--
---##################################################################################################################
--
--USE [<your admin database>]
--GO

--/****** Object:  Table [dba].[TableSpace] ******/
--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[<your admin schema>].[DB_TableSpace]') AND type in (N'U'))
--DROP TABLE [<your admin schema>].[DB_TableSpace]
--GO

--USE [<your admin database>]
--GO

--/****** Object:  Table [<your admin schema>].[DB_TableSpace]    Script Date: 11/01/2012 11:15:52 ******/
--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO

--CREATE TABLE [<your admin schema>].[DB_TableSpace](
--	[RunDate] [datetime] NOT NULL,
--	[DatabaseName] [nvarchar](128) NULL,
--	[SchemaName] [nvarchar](128) NULL,
--	[TableName] [nvarchar](128) NOT NULL,
--	[NumRows] [int] NOT NULL,
--	[Reserved] [nchar](16) NOT NULL,
--	[DataUsed] [nchar](16) NOT NULL,
--	[IndexUsed] [nchar](16) NOT NULL,
--	[Unused] [nchar](16) NOT NULL
--) ON [PRIMARY]

--GO
--ALTER TABLE [<your admin schema>].[DB_TableSpace] 
--ADD CONSTRAINT [DF_TableSpace_RunDate] 
--DEFAULT (getdate()) FOR [RunDate]
--GO
--
---######################################
-- 2 -- Collect the table size statistics
---##################################################################################################################
--
-- You will also need to create the following stored procedure in your admin database, that you can then either run 
-- on an adhoc basis, or schedule to run daily via the SQL Server Agent.
--
---##################################################################################################################
--
--use [DBAdmin]

create PROCEDURE [<your admin schema>].[dba_find_tableSpace]
AS

BEGIN

SET NOCOUNT ON;

DECLARE @SQL varchar(8000)

SELECT @SQL = '
IF ''@'' <> ''model'' AND ''@'' <> ''tempdb''
BEGIN
USE [@] EXECUTE sp_MSForEachTable ''INSERT INTO DBAdmin.dba.DB_TableSpace 
(TableName, NumRows, Reserved, DataUsed, IndexUsed, Unused) EXEC sp_spaceused ''''?'''';
UPDATE [<your admin database>].[<your admin schema>].DB_TableSpace SET SchemaName = LEFT(''''?'''', CHARINDEX(''''.'''', ''''?'''', 1) - 2) 
WHERE SchemaName IS NULL;
UPDATE [<your admin database>].[<your admin schema>].DB_TableSpace SET DatabaseName = ''''@'''' WHERE DatabaseName IS NULL; ''
END
'

EXEC sp_MSforeachdb @SQL, '@'

UPDATE [<your admin database>].[<your admin schema>].DB_TableSpace
SET SchemaName = REPLACE(SchemaName, '[', '')

END;

---##########################
-- 3 -- HTML Reporting
---##################################################################################################################
-- Usage...
--
-- Then, create this procedure to do the alerting. Again you can run it on an adhoc basis, or schedule it
-- via a SQL Agent job.
--
-- exec <your admin database>.<your admin schema>.dba_table_growth_report 15, 'hkingsland@mycompany.com'
--
---##################################################################################################################

USE [<your admin database>]
GO
/****** Object:  StoredProcedure [<your admin schema>].[dba_table_growth_report] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [<your admin schema>].[dba_table_growth_report]
 (
    @days int,-- number of days to report on
    @recipient_list varchar(2000) -- list of people to email
 )
as

BEGIN

DECLARE		@MailProfileName VARCHAR(50),		
			@ERR_MESSAGE varchar(200),
			@ERR_NUM int,
			@MESSAGE_BODY varchar(2000),
			@MESSAGE_BODY2 varchar(1000),
			@p_error_description varchar(300),
			@NewLine CHAR(2),
			@Q CHAR(1),
			@tableHTML VARCHAR(MAX),
			@tableHTML2 VARCHAR(MAX),
			@lineHTML VARCHAR(MAX),
			@lineHTML2 VARCHAR(MAX),
			@start_table VARCHAR(MAX),
			@start_table2 VARCHAR(MAX),
			@TR varchar(20),
			@END	 varchar(30),
			@END_TABLE varchar(30),
			@ENDTAB varchar(20),
			--@recipient_list	varchar(1000),
			@email varchar(100),
			@value varchar(30),
			@mailsubject varchar(200),
			@propertyid int,
			@userid bigint, 
			@property_value varchar(1000),
			@output VARCHAR(1000),
			@failsafe VARCHAR(100), -- failsafe operator to email
			@type varchar(10), 
			@dbname varchar(100),
			@tablename varchar(100),
			@minrows bigint,
			@maxrows bigint,
			@rowchange bigint,
			@GrowthAmt varchar(100),
			@startDate datetime,
			@endDate datetime,
			@td varchar(25);

SET @NewLine = CHAR(13) + CHAR(10) 
SET @Q = CHAR(39) 

-- initialize variables 

set @lineHTML = '' 
set @tableHTML = ''
set @start_table = ''
--set @days = 10

SET @tableHTML =
		'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Frameset// EN">' +
		'<html>' +
		'<LANG="EN">' +
		'<head>' +
		'<TITLE>SQL Administration</TITLE>' +
		'</head>' +
		'<body>'
		
set @start_table = '<font color="black" face="Tahoma" >' + 
	'<CENTER>' + 
	'<H1><font size=4>Table growth for the '+ @@servername + ' instance on ' + 
	CONVERT(VARCHAR(11),GETDATE(),113) + '</font></H1>' +
	'<table border="1">' +
	'<tr BGCOLOR="green">' + 
	-- list all table headers here
	'<th BGCOLOR="#0066CC" width="100%" colspan="7">Database Growth Details</th>'+'</tr>' + 
	'<tr>' + 
	'<th BGCOLOR="#99CCFF">Start Date</th>' + 
	'<th BGCOLOR="#99CCFF">End Date</th>' +
	'<th BGCOLOR="#99CCFF">Database Name</th>' + 
	'<th BGCOLOR="#99CCFF">Table Name</th>' +
	'<th BGCOLOR="#99CCFF">Start Rows</th>' +
	'<th BGCOLOR="#99CCFF">End Rows</th>' + 
	'<th BGCOLOR="#99CCFF">Amount or row change over period</th>' +
	'</tr>'

SET @TR = '</tr>'
SET @ENDTAB = '</table></font>'
--SET @END = '</table></font></body></html>'
SET @END_TABLE = '</table></font>'
SET @END = '</body></html>'

SET @mailsubject   = 'Database table growth trends for ' + @@servername + ' on ' + CONVERT(VARCHAR(11),GETDATE(),113)

SELECT @MailProfileName = name
FROM msdb.dbo.sysmail_profile WITH (NOLOCK)
where name like '<operator wild card>%'

PRINT @MailProfileName

	BEGIN TRY
	
DECLARE build_report CURSOR
FOR
select   a.mindate, --as 'Min Date',
         b.maxdate, --as 'Max Date',
         a.DatabaseName,
         b.TableName,
         a.minrows,
         b.maxrows,
         (b.maxrows - a.minrows) --as 'Amount or row change over period'
from     (select   distinct min(RunDate) as mindate,
                            DatabaseName,
                            --min(SchemaName),
                            TableName,
                            min(NumRows) as minrows
          from     <your admin database>.<your admin schema>.DB_TableSpace
          where RunDate < GETDATE() - @days
          --where TableName = 'dbcc_history'
          group by DatabaseName, TableName
          ) as a
         inner join
         (select   distinct max(rundate) as maxdate,
                            DatabaseName,
                            --max(SchemaName),
                            TableName,
                            max(NumRows) as maxrows
          from     <your admin database>.<your admin schema>.DB_TableSpace
          --where TableName = 'dbcc_history'
          group by DatabaseName, TableName
          ) as b
         on (a.tablename = b.tablename
             and a.DatabaseName = b.DatabaseName)
order by a.DatabaseName;

		-- Open the cursor.
		OPEN build_report;

		-- Loop through the update_stats cursor.

		FETCH NEXT
		   FROM build_report
		   INTO  @startDate, 
		   @endDate,
		   @dbname,
		   @tablename,
		   @minrows,
		   @maxrows,
		   @rowchange

		--PRINT 'Fetch Status is ... ' + CONVERT(VARCHAR(10),@@FETCH_STATUS)

		WHILE @@FETCH_STATUS <> -1 -- Stop when the FETCH statement fails or the row is beyond the result set
		BEGIN

			IF @@FETCH_STATUS = 0 -- to ignore -2 status "The row fetched is missing"
			BEGIN

				set @lineHTML = RTRIM(LTRIM(@lineHTML)) + 
								'<tr>' + 
								'<td>' +  ISNULL(left( convert (char(20) ,@startDate, 113 ), 17),'NOVAL') + '</td>' +
								'<td>' +  ISNULL(left( convert (char(20) ,@endDate, 113 ), 17),'NOVAL') + '</td>' +
								'<td>' +  ISNULL(cast(@dbname as varchar(50)),'NOVAL') + '</td>' +
								'<td>' +  ISNULL(cast(@tablename as varchar(50)),'NOVAL') + '</td>' +
								'<td>' +  ISNULL(cast(@minrows as varchar(10)),'NOVAL') + '</td>' +
								'<td>' +  ISNULL(cast(@maxrows as varchar(10)),'NOVAL') + '</td>' +
								'<td>' +  ISNULL(cast(@rowchange as varchar(10)),'NOVAL') + '</td>' +
								+ '</tr>'
								
				set @lineHTML = REPLACE( @lineHTML, '<td>', '<td BGCOLOR=#33FF99>' );
		
				print @lineHTML

			END
		
		FETCH NEXT
		   FROM build_report
		   INTO  @startDate, 
		   @endDate,
		   @dbname,
		   @tablename,
		   @minrows,
		   @maxrows,
		   @rowchange

		END

		-- Close and deallocate the cursor.

		CLOSE build_report;
		DEALLOCATE build_report;

-- 03/02/2012 -- changed to check the contens of the @linehtml variable, so that it will not generate an email if there
-- is not data to display.

		IF (@lineHTML != '' and @lineHTML is not NULL)
		BEGIN
	
			set @tableHTML = RTRIM(LTRIM(@tableHTML)) + @start_table + RTRIM(LTRIM(@lineHTML)) + @END_TABLE + @END

			-- as the <td> tags are auto-generated, I need to replace then with a new <td>
			-- tag including all the required formatting.

			--set @tableHTML = REPLACE( @tableHTML, '<td>', '<td BGCOLOR=yellow>' );
			
			print @tableHTML
		
			IF @recipient_list IS NULL 
			or @recipient_list = ''
			BEGIN
			
				SELECT @recipient_list = email_address
				FROM msdb..sysoperators
				WHERE name = '<your operator here>' -- Name of main required operator
				
				IF @recipient_list IS NULL
				BEGIN
							
					EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
												 N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
												 N'AlertFailSafeOperator',
												 @failsafe OUTPUT,
												 N'no_output'

					SELECT @recipient_list = email_address
					FROM msdb..sysoperators
					WHERE name = @failsafe
					                             
				END
			END
			
			PRINT @recipient_list
			
				EXEC msdb.dbo.sp_send_dbmail
					@profile_name = @MailProfileName,
					--@recipients = 'hkingsland@laingorourke.com',
					@recipients = @recipient_list,
					@body_format = 'HTML',
					@importance = 'HIGH',
					@body = @tableHTML,
					@subject = @mailsubject
					
		END
		
	END TRY

	BEGIN CATCH
	
	print 'Error Code is ... ' + RTRIM(CONVERT(CHAR(10),@ERR_NUM)) + ' Error Message is ... ' + @ERR_MESSAGE
	
		SELECT @ERR_MESSAGE = ERROR_MESSAGE(), @ERR_NUM = ERROR_NUMBER();
		SET @MESSAGE_BODY='Error running the ''Database Growth Report'' ' 
		+  '. Error Code is ... ' + RTRIM(CONVERT(CHAR(10),@ERR_NUM)) + ' Error Message is ... ' + @ERR_MESSAGE
		SET @MESSAGE_BODY2='The script failed whilst running against the... ' + LTRIM(RTRIM(cast(@@SERVERNAME as VARCHAR(30)))) + ' instance'
		SET @MESSAGE_BODY = @MESSAGE_BODY -- + @MESSAGE_BODY3

		EXEC msdb.dbo.sp_notify_operator 
			@profile_name = @MailProfileName, 
			@name=N'Haden Kingsland',
			@subject = @MESSAGE_BODY2, 
			@body= @MESSAGE_BODY
	
	-- If for some reason this script fails, check for any temporary
	-- tables created during the run and drop them for next time.
	
		IF object_id('tempdb..#temp_vm_create') IS NOT NULL
		BEGIN
		   DROP TABLE #temp_vm_create
		END

	END CATCH
	
END

Rate

4 (2)

Share

Share

Rate

4 (2)