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 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