Monitoring Table Size Growth in SQL Server

Execution

1.  Save the data to a table

– Execute sp_TableSize against all databases

– typically by a weekly job

INSERT TableSizeHistory (l1,[Database], [Schema], [Table], row_count, reserved_MB, data_MB,index_size_MB,unused_MB )

EXEC PerfDB.dbo.ExecuteDatabasesCommand @cmd = N’[sp_TableSize];’;

2.  Retrieve the data from the table

– Retreive data growth table level informtion

EXEC Perfdb.dbo.DataGrowth 

,@StartDate     = ’20100101′

,@EndDate       = ’20100201′

,@Delta_MB = 300; — look for tables with a data growth greater than this threshold

– You can also use the procedure with the default parameter values which work on the last 30 days and look for tables with a data growth greater than 200 MB

EXEC Perfdb.dbo.DataGrowth;

– You can execute it from a monitoring tool daily with a threshold that is not excpected to be met which should return an empty result set. If the result set is not empty you have crossed the predefined threshold and trigger a notification email

DECLARE @StartDate datetime, @EndDate datetime , @Delta_MB int, @Database bit;

SELECT  @StartDate = CURRENT_TIMESTAMP - 9 ,@EndDate = CURRENT_TIMESTAMP, @Delta_MB = 100, @Database = 0;

 EXEC PerfDB.dbo.DataGrowth

       @StartDate = @StartDate

      ,@EndDate   = @EndDate

      ,@Delta_MB  = @Delta_MB

      ,@Database  = @Database;