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 > 500 MB
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 500 MB.
Sample alert:
Code:
procedure Main;
var
ADatabase: TDatabase; // store a database reference
GrowthStats: TGrowthStats; // store a growth statistics record
Growth: Integer; // store the growth value in bytes
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);
// get the average growth over the last 2 days
Growth := ADatabase.AverageDailyGrowth(48, GrowthStats);
if (Growth > (500 * MB)) and (GrowthStats.Days >= 1.95) then
begin
RaiseAlert('Average growth of database is ' + FormatBytes(Growth) + ' over the
last ' + FloatToStr(GrowthStats.Days) + ' days.', ADatabase);
end;
end;
end;
Walk-through:
In the main code block, loop through each database 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 2 days old, so we use 48 hours as the
cut-off point. SQL Size will use the first data point that is at least 48 hours old or less.
Growth := ADatabase.AverageDailyGrowth(48, GrowthStats);
Check if the average daily growth is larger than 500 MB...
if (Growth > (500 * MB)) and ...
... and that the last collected data point is at least 47 hours old. We use a cut-off point of 47 hours because we don’t want
to use data that is too recent.
... (GrowthStats.Days >= 1.95) then
begin
...
end;
Raise an alert if the above 2 conditions are met. The growth value is also displayed in the alert.
RaiseAlert('Average growth of database is ' + FormatBytes(Growth) + ' 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