SQL Server alerts:
Send SQL query output as HTML table in an email body using database mail.
if object_id('tempdb..#DiskAlert') is not null
drop table #DiskAlert
select
distinct
ServerProperty('machinename') ServerName,
volume_mount_point,
total_bytes/1024/1024/1024 DiskSizeGB,
available_bytes/1024/1024/1024 FreeGB,
cast(cast((available_bytes/1024/1024/1024) as decimal(10,2))*100/(total_bytes/1024/1024/1024) as decimal(10,2)) FreePCT
INTO #DiskAlert
from sys.master_files as f
cross apply sys.dm_os_volume_stats(f.database_id, f.file_id);
if (select count(*) from #DiskAlert where FreePCT <90)>0
begin
declare @listOfRecipients varchar(max)
,@body nvarchar(max)
,@subject varchar(250)
,@MAIL_BODY VARCHAR(1000)
,@TableTail varchar(1000)
select @subject= 'Disk Space Alert'
SET @TableTail = '</table></body></html>' ;
SET @MAIL_BODY ='<p style="text-align:center;font-size:30px;color:red;background-color:yellow"><b><u> Disks with less than 90 percent free space</u></b></p>' +'<table border="10" align="left" cellpadding="10" cellspacing="10" style="color:black;font-family:consolas;text-align:center;">' +
'<tr>
<th bgcolor=#85C1E9>ServerName</th>
<th bgcolor=#85C1E9>Volume_Mount_Point</th>
<th bgcolor=#85C1E9>DiskSizeGB</th>
<th bgcolor=#85C1E9>FreeGB</th>
<th bgcolor=#85C1E9>FreePCT</th>
</tr>'
select @body =
(
select
td= ServerName,
td = volume_mount_point,
td=DiskSizeGB,
td=FreeGB,
td = cast(FreePCT as varchar)
from #DiskAlert
where FreePCT <90
for XML raw('tr'), elements
)
select @body=@MAIL_BODY + ISNULL(@Body, '') + @TableTail
select @listOfRecipients =
'Recipients email ids'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'Database mail profile name'
,@recipients = @listOfRecipients
,@subject = @subject ,
@body=@body,
@body_format = 'HTML'
end