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;     

Below is a sample result set from executing the above:

Pages: 1 2




Related Articles :

One Response to “Monitoring Table Size Growth in SQL Server”

  1. What is the interpretation of the l1 column? I notice it contains 1s and 0s. Thanks.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |