SQL Server Job alert

62 Views Asked by At

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
0

There are 0 best solutions below