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;
Below is a sample result set from executing the above:
]]>