Monitoring Table Size Growth in SQL Server


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:

Pages: 1 2


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