Monitoring Table Size Growth in SQL Server


1.  Save the data to a table

— Execute sp_TableSize against all

— 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

— 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;

@Delta_MB = 100,
@Database = 0;

 EXEC PerfDB.dbo.DataGrowth

       @StartDate =

      ,@EndDate   = @EndDate

      ,@Delta_MB  = @Delta_MB

      ,@Database  = @Database;     

Below is a sample result set from executing the above:

Pages: 1 2


No comments yet... Be the first to leave a reply!