POSTED : November 18, 2016
BY : Jim Egan

Most large IT shops have a tool that monitors servers for the admins and reports on issues such as low disk space.  Smaller shops, or non-production servers in large shops, may not have this.  Running out of disk space on a job server is a fatal situation.  Jobs will simply stop running with no explanation and they likely will not recover once space is restored.

To be proactive, I wrote a disk space monitoring job for my Data Services clients.  This is specific to windows but could be adapted for Linux.  If any local drive in the system falls below a global variable’s threshold of 100 megabytes, the job will send an email.  To send email from an ETL job you’ll have to configure the SMTP settings in the Server Manager utility.  Be sure you test this – don’t assume that sending email from the job server is working.

 

Here are the variable declarations.

 

Be sure you set default values for the two global variables.  For the email recipient variable, I prefer to use an email group/alias instead of individual email addresses.

 

$LV_Drive_Space_Error = ”;

 

# Get the list of drives

# The command should return a value like this: “0:   Drives: A:\ C:\ E:\ Z:\”

$LV_All_Drives = ltrim_blanks(rtrim_blanks(exec(‘fsutil.exe’, ‘ fsinfo drives’, 8)));

 

# Check for an O/S error encountered when getting the list of drives

if (substr($LV_All_Drives, 1, 1) = ‘0’)

begin

$LV_All_Drives = substr($LV_All_Drives, index($LV_All_Drives, ‘Drives:’, 1) + length(‘Drives: ‘), 99);

print($LV_All_Drives);

 

# Pick off the first drive in the list.  We want to end up with just “A:”.

$LV_Drive_Counter = 1;

$LV_Current_Drive = substr(ltrim_blanks(rtrim_blanks(word_ext($LV_All_Drives, $LV_Drive_Counter, ‘ ‘))), 1, 2);

while ( length($LV_Current_Drive) > 0 )

begin

# Get the type of drive

$LV_Drive_Type = ltrim_blanks(rtrim_blanks(exec(‘fsutil.exe’, ‘ fsinfo driveType ‘ || $LV_Current_Drive, 8)));

 

# Check to make sure the O/S command was successful

if (substr($LV_Drive_Type, 1, 1) = ‘0’)

begin

# Check to make sure this is a local fixed drive.  Ignore floppy, CD/DVD and network drives.

if (index($LV_Drive_Type, ‘Fixed Drive’, 1) > 0)

begin

# This will return the first line generated by the utility command.

# The return value will look like this: Total # of free bytes        : 55272587264

$LV_Drive_Free_Space_Str = ltrim_blanks(rtrim_blanks(exec(‘fsutil.exe’, ‘volume diskfree ‘ || $LV_Current_Drive, 8)));

 

# Check to make sure the O/S command was successful

if (substr($LV_Drive_Free_Space_Str, 1, 1) = ‘0’)

begin

$LV_Drive_Free_Space = cast(ltrim_blanks(rtrim_blanks(word_ext($LV_Drive_Free_Space_Str, 2, ‘:’))), ‘decimal(14,0)’);

 

# Does the drive have fewer megabytes of space available than the defined threshold?

# DOS reports total bytes.  Convert the megabyte threshold to bytes.

if ($LV_Drive_Free_Space < $GV_DiskSpace_LowValue_MB * 1024 * 1024)

$LV_Drive_Space_Error = $LV_Drive_Space_Error || ‘Drive ‘ || $LV_Current_Drive || ‘ has only ‘ || cast($LV_Drive_Free_Space, ‘varchar(15)’) || ‘ bytes remaining.’ || chr(13)||chr(10);

end

else

begin

raise_exception(‘Failed to get the disk free space for drive ‘ || $LV_Current_Drive);

end

end

end

else

begin

raise_exception(‘Failed to determine the drive type of drive ‘ || $LV_Current_Drive);

end

 

# Work on the next drive in the list

$LV_Drive_Counter = $LV_Drive_Counter + 1;

$LV_Current_Drive = substr(ltrim_blanks(rtrim_blanks(word_ext($LV_All_Drives, $LV_Drive_Counter, ‘ ‘))), 1, 2);

 

end

end

else

begin

raise_exception(‘The job failed to obtain a list of drives. Error: ‘ || $LV_All_Drives);

end

 

# Send an email only if there is something to report

if (length($LV_Drive_Space_Error) > 0)

smtp_to($GV_Email_Recipient, ‘Low Disk Space Notification (‘ || get_env(‘COMPUTERNAME’) || ‘) ‘, $LV_Drive_Space_Error, 0, 0);

#         Use an exception if your job server is unable to send email.

#         raise_exception(‘Low Disk Space Notification (‘ || get_env(‘COMPUTERNAME’) || ‘): ‘ || $LV_Drive_Space_Error);