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