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 data file path has < 1 GB of free space
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 free space of the paths containing the tempdb database files has less than 1 gigabyte of free
space available.
Sample alert:
Code:
procedure Main;
var
ADatabase: TDatabase; // store the details of a database
DBFile: TDBFile; // store the details of a database file
APath: TPath; // store the details of a path
FreeSpace: Integer; // store a paths’ available free space
f: Integer; // loop variable
begin
// get a reference to the tempdb database
ADatabase := Instance.DatabaseByName('tempdb');
for f := 0 to ADatabase.DBFileCount - 1 do // loop though all the tempdb files
begin
DBFile := ADatabase.DBFile(f); // get a reference to each file
if DBFile.FileType = ftRows then // if the file is a data file...
begin
APath := DBFile.Path; // get a reference to the path
FreeSpace := APath.FreeSpace; // get the available space for that path
if FreeSpace < (1 * GB) then // check if free space is > 1 GB
begin
RaiseAlert('The path (' + APath.Name + ') containing the tempdb data file
(' + DBFile.PhysicalName + ') has less than 1 GB of free space (' +
FormatBytes(FreeSpace) + ')', Instance);
end;
end;
end;
end;
Walk-through:
In the main code block, first get a reference to the tempdb database.
// get a reference to the tempdb database
ADatabase := Instance.DatabaseByName('tempdb');
Loop through all the files belonging to tempdb.
for f := 0 to ADatabase.DBFileCount - 1 do // loop though all the tempdb files
begin
...
end;
Get a reference to each database file in the loop.
DBFile := ADatabase.DBFile(f); // get a reference to each file
If the database file is a data file (we ignore transaction logs for tempdb), process it.
if DBFile.FileType = ftRows then // if the file is a data file...
begin
...
end;
Get a reference to the path the database file is stored on.
APath := DBFile.Path; // get a reference to the path
Get the amount of free space available on that path.
FreeSpace := APath.FreeSpace; // get the available space for that path
Check if the amount of free space on that path is less than 1 gigabyte.
if FreeSpace < (1 * GB) then // check if free space is > 1 GB
begin
...
end;
If it’s less, raise an alert. The alert also provides us the path name and the currently available free space.
RaiseAlert('The path (' + APath.Name + ') containing the tempdb data file (' +
DBFile.PhysicalName + ') has less than 1 GB of free space (' + FormatBytes(FreeSpace) +
')', Instance);
•
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