Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unable to see the available space value in @alertMessage Expand / Collapse
Author
Message
Posted Sunday, November 10, 2013 8:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 8:47 AM
Points: 3, Visits: 38
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
Post #1512997
Posted Monday, November 11, 2013 6:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 8:47 AM
Points: 3, Visits: 38
Hello Guru's,

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

Thanks,
Post #1513123
Posted Monday, November 11, 2013 7:33 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:07 AM
Points: 868, Visits: 2,775
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

@SeanPearceSQL

About Me
Post #1513137
Posted Monday, November 11, 2013 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 8:47 AM
Points: 3, Visits: 38
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
Post #1513149
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse