How can I set up a job alert in SQL Server to an email when any agent SQL Server jobs in a server stops running after 30 mins threshold?
I tried with this script:
DECLARE @profile_name VARCHAR(MAX) = 'Default Profile;
DECLARE @email_to_address VARCHAR(MAX) = '[email protected]';
DECLARE @email_subject VARCHAR(MAX);
DECLARE @email_body VARCHAR(MAX);
WAITFOR DELAY '00:30:00'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name,`your text`
@recipients = @email_to_address,
@subject = @email_subject, `your text`
@body_format = 'html',
@body = @email_body;
SELECT @email_subject = 'Missed Job Alert: ' + ISNULL (@@SERVERNAME, CAST(SERVERPROPERTY('ServerName') AS VARCHAR(MAX)));
SELECT @email_body = 'At least one job run was missed on ' + ISNULL(@@SERVERNAME, CAST(SERVERPROPERTY('ServerName') AS VARCHAR(MAX))) + during the period ' + CAST(@start_time_local AS VARCHAR(MAX))
END