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 transaction log is > 50% of total database size
Details
A databases’ transaction log is used to store transactions prior to them being committed and/or backed, depending on the
databases’ recovery model. A large transaction log may indicate a long-running transaction, and/or a long overdue backup.
This script raises an alert if your transaction log size is more then half your database size. The alert also provides details of
the transaction log size and the database size.
Sample alert:
Code:
procedure Main;
var
ADatabase: TDatabase; // store a database reference
f: Integer; // loop variable
begin
for f := 0 to Instance.DatabaseCount - 1 do
begin
// get a reference to each database in the loop
ADatabase := Instance.Database(f);
if ADatabase.LogSize > (ADatabase.Size / 2) then
begin
RaiseAlert('Transaction log (' + FormatBytes(ADatabase.LogSize) + ') > half of
database size (' + FormatBytes(ADatabase.Size) + ').', ADatabase);
end;
end;
end;
Walk-through:
In the main code block, loop through each database belonging to the current instance.
for f := 0 to Instance.DatabaseCount - 1 do
...
end;
Get a reference to each database.
// get a reference to each database in the loop
ADatabase := Instance.Database(f);
Check if the databases’ transaction log size is larger than half the database size.
if ADatabase.LogSize > (ADatabase.Size / 2) then
begin
...
end;
If it is, we raise an alert, an also show the transaction log size and the database size.
RaiseAlert('Transaction log (' + FormatBytes(ADatabase.LogSize) + ') > half of database
size (' + FormatBytes(ADatabase.Size) + ').', ADatabase);
•
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