T-SQL Solution:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[spaAlertSQLJobFailures]
@LastNumMins int = -15,
@Debug bit = 0
as
Set NoCount On
--Exec dbo.spaAlertSQLJobFailures -15, 0
Declare @EmailSubject varchar(100) = 'SQL Job(s) failures'
Declare @EmailBodyMsg nvarchar(100)
Declare @EmailBody nvarchar(max)
Declare @ProfileName varchar(100)
Declare @Recipientslist varchar(100)
Set @ProfileName = (Select value from dbo.Config where [Name] = 'PROFILENAME')
Set @Recipientslist = (Select value from dbo.Config where [Name] = 'SQL Jobs Monitor')
If Exists (SELECT 1 FROM msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
WHERE T1.run_status NOT IN (1, 4) AND T1.step_id != 0
AND msdb.dbo.agent_datetime(T1.run_date, T1.run_time) BETWEEN (SELECT DATEADD (mi, @LastNumMins, GETDATE())) AND (SELECT GETDATE())
)
Begin
Set @EmailSubject = 'Alert: SQL Job Failure: ' + @@SERVERNAME
SELECT @EmailBodyMsg = '<H3><u>'+'SQL Job(s):'+'</u></H3>'+ Char(10) + Char(10)
SELECT @EmailBody = COALESCE(@EmailBody + '^ ', '') +
CAST('<H4>'+t2.name+'</H4>'+Char(10)+'<font color=red>'+Substring(t1.message,1,1000)+ '</br> [Time: '
+ CAST(msdb.dbo.agent_datetime(run_date, run_time) as NVARCHAR(MAX)) + '] </br> [Duration: '
+ CAST(((run_duration/10000 * 3600 + (run_duration/100) % 100 * 60 + run_duration % 100 + 31 ) / 60 * 60) as NVARCHAR(MAX))
+ ' s]'+'</font>'
AS NVARCHAR(max))
FROM
msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
WHERE T1.run_status NOT IN (1, 4)
AND T1.step_id != 0
AND msdb.dbo.agent_datetime(T1.run_date, T1.run_time) BETWEEN (SELECT DATEADD (mi, @LastNumMins, GETDATE())) AND (SELECT GETDATE())
Set @EmailBody = Replace (@EmailBody, '^', Char(10)+Char(10))
Set @EmailBody = @EmailBodyMsg + ' ' + @EmailBody
Set @EmailBody = @EmailBody + '</br></br></br>' + '- SQL Job Monitor on ' + @@SERVERNAME + '</br></br></br></br>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients = @Recipientslist,
@subject = @EmailSubject,
@body = @EmailBody,
@body_format = 'HTML',
@importance= 'High'
End
Email:
|
|
|
|
|
|