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 projected growth over next 14 days exceeds disk capacity
Details:
A databases’ data files will grow by a specific %, or by a specific size. This script raises an alert if any of the paths cannot
store the projected growth for the next 14 days of all databases.
There are 2 main blocks in the code. The first block collects the total expected growth value for all databases, and stores
them to the paths where the database files are located. The second block compares the expected growth requirements
for each path against the current free space available on each path.
Sample alert:
Code:
procedure Main;
var
ADatabase: TDatabase; // store a database reference
ADBFile: TDBFile; // store a database file reference
APath: TPath; // store a path reference
AverageGrowthValue: Integer; // store a growth value in bytes
ProjectedGrowth: Integer; // store the projected growth value in bytes
AlertID: Integer; // store the Alert ID so that we can add alert details later
GrowthStats: TGrowthStats;
f, g, i: Integer; // loop variables
begin
for f := 0 to Instance.DatabaseCount - 1 do
begin
// get a reference to each database in the loop
ADatabase := Instance.Database(f);
for g := 0 to ADatabase.DBFileCount - 1 do
begin
ADBFile := ADatabase.DBFile(g); // get a reference to each file
// get the average daily growth rate for the last 14 days (336 hours)
AverageGrowthValue := ADBFile.AverageDailyGrowth(336, GrowthStats);
// only take into account the growth rate if the last data point was 3
// days or older
if GrowthStats.Days > 3 then
begin
// estimate the growth rate for the next 14 days
ProjectedGrowth := AverageGrowthValue * 14;
// use the TPath Tag property to store the cumulative growth value.
ADBFile.Path.Tag := ADBFile.Path.Tag + ProjectedGrowth;
// use the TDBFile Tag property to store the files’ growth value.
ADBFile.Tag := ProjectedGrowth;
end;
end;
end;
// check if any paths' free space is less than the projected growth for the next
// 2 extents' growth
for f := 0 to Instance.PathCount - 1 do
begin
APath := Instance.Path(f); // get a reference to the path
// We used the Tag property is store the projected growth.
// Compare this to the current FreeSpace property
if (APath.Tag > APath.FreeSpace) then
begin
AlertID :=
RaiseAlert('The path (' + APath.Name + ') free space (' +
FormatBytes(APath.FreeSpace) + ') is less than the projected growth (' +
FormatBytes(APath.Tag) + ') for the next 14 days.', APath);
// display projected growth for all data files on this path
for g := 0 to Instance.DatabaseCount - 1 do
begin
for i := 0 to Instance.Database(g).DBFileCount - 1 do
begin
if Instance.Database(g).DBFile(i).Path.Name = APath.Name then
begin
if Instance.Database(g).DBFile(i).Tag > 0 then
begin
AddAlertDetails(AlertID, ' - database file (' +
Instance.Database(g).DBFile(i).Name + ') projected growth: ' +
FormatBytes(Instance.Database(g).DBFile(i).Tag));
end;
end;
end; // Instance.Database(g).DBFileCount - 1
end; // Instance.DatabaseCount - 1
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.
ADatabase := Instance.Database(f);
For each database, loop through each of the databases’ files.
for g := 0 to ADatabase.DBFileCount - 1 do
begin
...
end;
Get a reference to each of the file in the loop.
ADBFile := ADatabase.DBFile(g); // get a reference to each file
Get the average growth value for the last 14 days.
// get the average daily growth rate for the last 14 days (336 hours)
AverageGrowthValue := ADBFile.AverageDailyGrowth(336, GrowthStats);
Only continue processing if the last data point was older than 3 days.
// only take into account the growth rate if the last data point was 3
// days or older
if GrowthStats.Days > 3 then
begin
...
end;
Calculate the projected growth for the next 14 days, using the average daily growth value we obtained earlier.
// estimate the growth rate for the next 14 days
ProjectedGrowth := AverageGrowthValue * 14;
We want to use this value later, so we store it in 2 places. Once in the Paths’ Tag property, so that we now how much
projected space we need on that path for all databases...
// use the TPath Tag property to store the cumulative growth value.
ADBFile.Path.Tag := ADBFile.Path.Tag + ProjectedGrowth;
And once more in the database files’ Tag property, because we want to display in the alert which database files make up
the growth requirements for that path.
// use the TDBFile Tag property to store the files’ growth value.
ADBFile.Tag := ProjectedGrowth;
At the end of these 2 loops, we would have calculated the expected growth for the next 14 days for all databases. The
total growth value is stored in the Tag property of each path, so we know how much free space is required for each path
on the instance.
Now loop through each path on the instance.
for f := 0 to Instance.PathCount - 1 do
begin
...
end;
Get a reference to each path.
APath := Instance.Path(f); // get a reference to the path
Compare the paths’ Tag property, which is storing the total growth requirements for all databases’ next 2 growth extents,
against the current paths’ free space.
// We used the TAG property is store the projected growth.
// Compare this to the current FREESPACE property
if (APath.Tag > APath.FreeSpace) then
begin
...
end;
If the required space exceeds the available space, raise an alert. The alert will contain details of the projected space
requirement, and the current availble free space. We store the alert IDs’ value, so that we can add more details to the
alert later on.
AlertID :=
RaiseAlert('The path (' + APath.Name + ') free space (' + FormatBytes(APath.FreeSpace)
+ ') is less than the projected growth (' + FormatBytes(APath.Tag) + ') for the next
14 days.', APath);
We display details of the database files that contribute to the growth requirement on this path.
// display projected growth for all data files on this path
for g := 0 to Instance.DatabaseCount - 1 do
begin
for i := 0 to Instance.Database(g).DBFileCount - 1 do
begin
if Instance.Database(g).DBFile(i).Path.Name = APath.Namet then
begin
if Instance.Database(g).DBFile(i).Tag > 0 then
begin
AddAlertDetails(AlertID, ' - database file (' +
Instance.Database(g).DBFile(i).Name + ') projected growth: ' +
FormatBytes(Instance.Database(g).DBFile(i).Tag));
end;
end;
end; // Instance.Database(g).DBFileCount - 1
end; // Instance.DatabaseCount - 1
•
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