Get Free Space for particular drive and send email

Below script gives you the free space for D drive. You can run this on any machine using SSMS.
The threshold of 300 can be changed as per your requirement

Use tempdb
Declare
@Free_space  as varchar(max),
@Body as varchar(255)
If OBJECT_ID('#Fixeddrives') is not null
BEGIN
 Drop Table #Fixeddrives
 Create Table #Fixeddrives
  (
   drives varchar(2),
   FreeSpace bigint
  )

 INSERT INTO #Fixeddrives
 Exec sys.xp_fixeddrives
 Select @Free_space =  FreeSpace/1024
 From #Fixeddrives
 Where drives='D' and (FreeSpace)/1024< = 300
 Drop Table #Fixeddrives
END
if @Free_space < 300
 BEGIN
  Select @Body = 'SERVER NAME - Total Free space on D drive is: ' + @Free_space + ' GB'
  EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'PROFILE NAME',
  @recipients ='TO EMAIL ADDRESS',
  @body = @Body,
  @subject = 'SERVER NAME - Low disk space',
  @Importance = 'HIGH'
 END

Get Current SQL Job running on a server

Run the below script in your SQL Server:


msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL