0

In SQL server 2008R2, I have a JOB which is running every month 1st week for Reports and I am frequently receiving Error message like "The job failed. The Job was invoked by Schedule 14. The last step to run was step 1" and Failed step error details: "Message: Unable to connect to SQL Server '(local)'. The step failed" Finally I come to know Reports failing because of Database is not fully Restored at the time, when Report schedule starts. After I manually started the Process and Jobs run successfully.

I wan to know when Report jobs failure and when it happen I need to receive some Email alerts (2 types of Error E-mails needed - one for Errors and another one for Informational etc..), Can I Setup E-mail alerts for Jobs?

SQL server Agent Notification and Setting up an Operator is good Idea? or there is any preferable ways to do that?

Improve this question
1
  • 2
    I'd recommend increasing the 'retries' for the step that keeps failing - you can specify an amount of time to wait between 'retries'. That might allow the process to eventually complete successfully without human intervention - check out this link for setting up notifications for failed jobs. brentozar.com/blitz/setting-failure-emails-on-jobs – Scott Hodgin - Retired Jun 12 '17 at 18:48
0

You are correct that is the best way for individual server.

Sample code for notification when a job fail.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @jobname=N'test', 
        @notify_level_email=2, 
        @notify_level_netsend=2, 
        @notify_level_page=2, 
        @notify_email_operator_name=N'sysadmin'
GO

You can send email for success/failure or both.

0 Never

1 On success

2 On failure

3 Always

Books online has details.

There are large shops where instead of setting up notification for each SQL Agent jobs, all failed jobs will be collected at xx minute interval and send one email/raise ticket in the form of a report.

Improve this answer
5
  • So Is it okay for Setup 2 types of Error E-mails - one for Errors and another one for Informational etc through SQL server Agent Notifications? – CR241 Jun 12 '17 at 18:55
  • Error you mean job failed? Not sure what you mean by informational? – SqlWorldWide Jun 12 '17 at 18:55
  • Yes, Error message for Job failure. Informational means if job succeeded or in every steps we can get some notification saying that Step completed or failed messages. Is it possible? – CR241 Jun 12 '17 at 18:58
  • You can send email for failure/success or both. See my modified answer. For step details you need to set up logging or custom code if you need email for each step. See this. – SqlWorldWide Jun 12 '17 at 19:01
  • 1
    Using the options in SQL Server jobs, you can send an email when the job fails, or when the job succeeds, or when the job completes. You cannot send one type of email when the job succeeds, and another when the job fails. the best way to work around this would be not to use the alerts as the SQL Server Agent sends them out, but add steps to use xp_sendmail to send detailed messages. You might just use this for the informational, and use the default in case of failure (in particular, in case the send mail step fails!) – RDFozz Jun 12 '17 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.