SQL SIZE
Products
Support
Get our Products
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
Company
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

Download trial Download trial FREE license! See help file See help file Scripts library Scripts library