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 TEMPDB > 1 GB
Details:
The SQL Server tempdb database is used for many functions, including storing temporary work tables for sorting
operations, temporary user objects, and row versioning data.
Sometimes, the tempdb database may grow unexpectedly large due to a new query introduced in an application. This
script raises an alert if the tempdb database grows larger than 1 gigabyte. The alert also provides the current tempdb size.
Sample alert:
Code:
procedure Main;
var
DBSize: Integer; // store the database size
begin
DBSize := Instance.DatabaseSize('tempdb'); // get the tempdb size
if DBSize > (1 * GB) then
// check if its larger than 1 GB
begin
RaiseAlert('tempdb database is larger than 1 GB. Current size is ' +
FormatBytes(DBSize), Instance);
end;
end;
Walk-through:
In the main code block, store the tempdb database size in the DBSize variable.
DBSize := Instance.DatabaseSize('tempdb'); // get the tempdb size
Check if this value is larger than 1 GB.
if DBSize > (1 * GB) then
// check if its larger than 1 GB
begin
...
end;
If it is, raise an alert, which also tells us the current size of the tempdb database.
RaiseAlert('tempdb database is larger than 1 GB. Current size is ' + FormatBytes(DBSize),
Instance);
Pre-requisites:
•
ensure that your data collection settings include the tempdb database
•
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