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