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 database file is > 80% of maximum size
Details: SQL Server data files can be configured to auto-grow up to a specific size.  You can then use this script to raise an alert if the data file has reached 80% or more of the maximum allowable size. Sample alert: Code: procedure Main; var ADatabase: TDatabase// store a database reference ADBFile: TDBFile// store a database file reference CurrentSize, MaxSize: Integer;  // store integer values for sizes f, g: Integer;  // loop variables begin for f := 0 to Instance.DatabaseCount - 1 do begin //  get a reference to the current 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 the database file MaxSize := ADBFile.MaxSize//  get the maximum size in bytes   //  A value of 0 indicates no growth is allowed. //  A value of -1 indicates infinite growth until the disk is full. //  Thus, we only use the MaxSize value if it is larger than zero. if MaxSize > 0 then  begin //  get the current file size CurrentSize := ADatabase.DBFile(g).Size;   if CurrentSize > (MaxSize * 0.8) then  begin RaiseAlert('Database file is ' + FormatFloat('0.00', (CurrentSize / MaxSize) * 100) + '% of the maximum size.', ADBFile); end; end; end; end; end; Walk-through: In the main code block, loop through each path 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); Then loop through each of the databases’ files. for g := 0 to ADatabase.DBFileCount - 1 do  begin ... end; Get a reference to each database file. ADBFile := ADatabase.DBFile(g);  // get a reference to the database file Store the maximum size for the database file. MaxSize := ADBFile.MaxSize// get the maximum size in bytes A MaxSize value of 0 indicates no growth is allowed.  A value of -1 indicates that infinite growth is allowed.  Thus, only process the maximum value for this database file if the value is larger than zero.   if MaxSize > 0 then  begin ... end; Get the current size of the database file. CurrentSize := ADatabase.DBFile(g).Size// get the current file size Compare the current size against 80% of the maximum size. if CurrentSize > (MaxSize * 0.8) then  begin ... end; If larger, raise an alert. RaiseAlert('Database file is ' + FormatFloat('0.00', (CurrentSize / MaxSize) * 100) + '% of the maximum size.', ADBFile);
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