Copyright © 2005 - 2019 Yohz Software, a division of Yohz Ventures Sdn Bhd. ALL RIGHTS RESERVED.
All trademarks or registered trademarks are property of their respective owners
SQL Size script:
raise alert if average daily database growth is > 20%
Details:
A read-write database will usually experience some daily growth. This script raises an alert if a databases’ average daily
growth is larger than 20%.
Sample alert:
Code:
procedure Main;
var
ADatabase: TDatabase; // store a database reference
GrowthStats: TGrowthStats; // store a growth statistics record
Growth: Integer; // store a growth value
f: Integer; // loop variable
begin
for f := 0 to Instance.DatabaseCount - 1 do
begin
// get a reference to the current database in the loop
ADatabase := Instance.Database(f);
Growth := ADatabase.AverageDailyGrowth(24, GrowthStats);
if (GrowthStats.GrowthPercent > 20) and (GrowthStats.Days >= 0.96) then
begin
RaiseAlert('Average growth of database is ' +
FloatToStr(GrowthStats.GrowthPercent) + '% over the last ' +
FloatToStr(GrowthStats.Days) + ' days.', ADatabase);
end;
end;
end;
Walk-through:
In the main code block, loop through each path on the current instance.
for f := 0 to Instance.DatabaseCount - 1 do
begin
...
end;
Get a reference to each database in the loop.
// get a reference to the current database in the loop
ADatabase := Instance.Database(f);
For each database, get its growth statistics. We want to collect data that is at least a day old, so we use 24 hours as the
cut-off point. SQL Size will use the first data point that is at least 24 hours old or less.
Growth := ADatabase.AverageDailyGrowth(24, GrowthStats);
Check if the average daily growth percentage is larger than 20%:
if (GrowthStats.GrowthPercent > 20) ...
and that the last collected data point is at least 23 hours old. We use a cut-off point of 23 hours because we don’t want to
use data that is too recent.
... and (GrowthStats.Days >= 0.96) then
begin
...
end;
Raise an alert if the above 2 conditions are met. The growth percentage value is also displayed in the alert.
RaiseAlert('Average growth of database is ' + FloatToStr(GrowthStats.GrowthPercent) + '%
over the last ' + FloatToStr(GrowthStats.Days) + ' days.', ADatabase);
•
analyse database growth easily using charts and graphs
•
raise alerts when database growth exceed expectations
•
identify the largest and fastest growing data files quickly
•
have growth reports sent to you daily via email
•
supports SQL Server 2005 and newer versions
Analyse SQL Server database
and table growth using
SQL Size