/** This script checks the current space of the disks and if the available space is lower that the threshold (GB in this case) sends out email**/
use MyAdminDB
GO
declare @from varchar(100),
@to varchar(200),
@subject varchar(100),
@threshold int -- number of MB under which to launch an alert
SET NOCOUNT ON
DECLARE @list nvarchar(2000) = '';
WITH core AS (
SELECT DISTINCT
s.volume_mount_point [Drive],
CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]
FROM
sys.master_files f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
)
SELECT @list = @list + ' ' + Drive + ', current space available =' + cast(cast(round([AvailableMBs]*1.000/1024.000,2) as numeric(36,2)) as varchar) + 'GB'
FROM core
WHERE AvailableMBs < @threshold
IF LEN(@list) > 3 BEGIN
DECLARE @msg varchar(500) = 'Disk Space les than 8GB Notification. The following drives are currently reporting less than '
+ CAST(@threshold/1024 as varchar(12)) + ' GB free: ' + @list
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile@mydomain.com',
@recipients = @to,
@subject = @subject,
@body = @msg
END
RETURN 0
GO
Tuesday, April 26, 2016
Thursday, April 21, 2016
dbmail: sending html table in the email
Declare
@xml nvarchar(max),
@body nvarchar(max)
SET @xml = CAST(( SELECT clientcode AS 'td','',right(FilenameStr,charindex('\',reverse(FilenameStr),5)-1) as 'td','', cast(importstartDate as varchar) as 'td','',cast(ImportEndDate as varchar) as 'td'
,'', totnum as 'td', '', totdups as 'td','', totloaded as 'td','', totfail as 'td', '', totskip as 'td' FROM #fileList order by HospitalCode, importstartDate FOR XML PATH('tr'),ELEMENTS) as nvarchar(MAX))
--select @xml
SET @body ='<html><body><H3> files list for date ' + @sqllasjobrundate + '-' + convert(varchar(8),getdate(),112) + ' </H3>
<table border = 1>
<tr>
<td> Hospital code</td> <td> Fine name </td> <td> Import start date </td></td> <td> Import end date </td>
<td> Total count in file </td><td> Total duplicates </td><td> Total imported </td><td> Total failed validation </td><td> Total skipped </td>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMail@mycompany.com', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'mail1@mycompany.com', -- replace with your email address
@subject = 'file log Report ' ;
end
Subscribe to:
Posts (Atom)