Unable to see the available space value in @alertMessage

• sqlnewbee 46327

  SSC Rookie

  Points: 39

  More actions

  November 10, 2013 at 8:30 pm

  #279012

  Hi Guru's,

  I am new to this forum and having below issue.

  The below code is working fine but the problem is I am not able to see the available space value in @alertMessage through email.

  could you please help me?

  DECLARE @list nvarchar(2000) = '';

  WITH core AS (

  SELECT DISTINCT

  s.volume_mount_point [Drive],

  CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs],

  ((CAST(s.total_bytes / 1048576.0 as decimal(20,2)))*97)/100 FifteenpercentAvailableMBs

  FROM

  sys.master_files f

  CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s

  )

  SELECT @list = @list + ' ' + Drive

  FROM core

  WHERE AvailableMBs < FifteenpercentAvailableMBs

  DECLARE @alertMessage varchar(500)

  DECLARE @subject varchar(100)

  DECLARE @AvailableMBs int

  IF LEN(@list) > 3

  BEGIN

  SET @alertMessage ='Low Disk Space Notification. The following drives are currently reporting Lessthan15percentAvailableMBs,'+@list

  SET @Subject = 'Drive free space is low on server - ' + @@SERVERNAME

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'SQL Server Profile',

  @recipients ='xxx@gmail.com',

  @body =@alertMessage,

  @importance = 'High',

  @subject = @Subject;

  END

  --EXEC xp_fixeddrives

  Thanks,

  SQLNewBee

• sqlnewbee 46327

  SSC Rookie

  Points: 39

  More actions

  November 11, 2013 at 6:56 am

  #1665466

  Hello Guru's,

  no reply from any one. If you have any idea or solution please post.

  Thanks,

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  November 11, 2013 at 7:33 am

  #1665479

  The maths is not correct. Try this in your CTE.

  WITH core AS (

  SELECT DISTINCT

  s.volume_mount_point [Drive],

  s.available_bytes / 1024 / 1024 [AvailableMBs],

  s.total_bytes / 1024 / 1024 [TotalMBs],

  (s.available_bytes / 1024. / 1024) / (s.total_bytes / 1024. / 1024) * 100 AS AvailablePercent

  FROM

  sys.master_files f

  CROSS APPLY

  sys.dm_os_volume_stats(f.database_id, f.[file_id]) s

  WHERE

  (s.available_bytes / 1024. / 1024) / (s.total_bytes / 1024. / 1024) * 100 < 15)

  SELECT @list = @list + ' ' + Drive

  FROM core;

  

  The SQL Guy @ blogspot[/url]

  @SeanPearceSQL

  About Me[/url]

• sqlnewbee 46327

  SSC Rookie

  Points: 39

  More actions

  November 11, 2013 at 8:02 am

  #1665490

  Hi Sean Pearce,

  Thank you so much for your help. Now I am getting message that the command successful but no email. Earlier I use to get the email.

  Thank you once again,

  SQLNewBee