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 path cannot accomodate next 2 growth extents
Details:
A databases’ data files will grow by a specific %, or by a specific size. This script raises an alert if there is insufficient space
on the data files’ path to grow in size by 2 growth extents, for all databases where SQL Size is collecting data on.
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
GrowthValue: Integer; // store a growth value in bytes
AlertID: Integer; // store the Alert ID so that we can add alert details later
f, g, i: Integer; // loop variables
GrowthRate: Float; // store a growth rate
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
// growth value can be in number of bytes, or a percentage
GrowthValue := ADBFile.Growth;
if ADBFile.IsPercentGrowth then
begin
// growth rate is a percentage value
GrowthRate := 1 + (GrowthValue / 100); // get the growth rate in %
// calculate compound growth for next 2 extents
GrowthValue := Round(ADBFile.Size * Power(GrowthRate, 2)) - ADBFile.Size;
end
else begin
// calculate growth for next 2 extents growth.
GrowthValue := GrowthValue * 2;
end;
// use the TPath Tag property to store the cumulative growth value.
ADBFile.Path.Tag := ADBFile.Path.Tag + GrowthValue;
// use the TDBFile Tag property to store the files’ growth value.
ADBFile.Tag := GrowthValue;
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 2 extents'' growth.', 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 growth value for the file.
// growth value can be in number of bytes, or a percentage
GrowthValue := ADBFile.Growth;
Calculate the growth for the next 2 extents, depending on whether the growth value is a percentage or an absolute value.
The IsPercentGrowth property will indicate if the growth value is a percentage.
if ADBFile.IsPercentGrowth then
begin
// growth rate is a percentage value
GrowthRate := 1 + (GrowthValue / 100); // get the growth rate in %
// calculate compound growth for next 2 extents
GrowthValue := Round(ADBFile.Size * Power(GrowthRate, 2)) - ADBFile.Size;
end
else begin
// calculate growth for next 2 extents growth.
GrowthValue := GrowthValue * 2;
end;
Once we have computed the growth value, we store the value in the Tag property of the databases’ file Path. We add the
current value to the existing value in the Tag property because we want to calculate the expected growth for all databases.
// use the TPath Tag property to store the cumulative growth value.
ADBFile.Path.Tag := ADBFile.Path.Tag + GrowthValue;
We also store the growth value in the database files’ Tag property because we want to display in the alert the database
files that are located on that path, and what are their growth requirements.
// use the TDBFile Tag property to store the files’ growth value.
ADBFile.Tag := GrowthValue;
At the end of these 2 loops, we would have calculated the expected growth for the next 2 extents 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.
AlertID :=
RaiseAlert('The path (' + APath.Name + ') free space (' + FormatBytes(APath.FreeSpace)
+ ') is less than the projected growth (' + FormatBytes(APath.Tag) + ') for the next 2
extents'' growth.', APath);
We also display details of the database files that contribute to the growth requirement for 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