Esat Erkec

How to create an email alert in SQL Server Agent for job failures

November 28, 2018 by

SQL Server Agent is a Microsoft Windows service which helps to execute, schedule and automatize T-SQL queries, Integration Service Package, SQL Server Analysis Service queries, executable programs, operating system, and PowerShell commands. These actions which are performed by SQL Server can be called by the SQL Server Agent. Maybe, we can liken SQL Server Agent to an alarm clock because the agent will execute the scheduled task when the time comes

On the other hand, every database administrator or the person whose responsibility for managing SQL Server; needs to have some knowledge about SQL Server Agent. So that the database administrator can execute, schedule and automatize essential database maintenance operations through SQL Server Agent. In terms of essential database maintenance methodology, these operations should be done properly and regularly to avoid catastrophic failure. Such as; taking database backups or indexing are the regular database maintenance operations that can be automatized with help of SQL Server Agent. Regarding this idea, SQL Server Agent is the significant assistant tool for a database administrator.

Job Steps and Schedules are an integral part of SQL Agent jobs. A Job step can be defined as task or group of tasks which will be completed by SQL Agent, in addition to when we want to set up a job at least we need to do one job step. When we look at the output of the job step, it can report two results after the completed task. These are successful or failure. Schedules specify when a job will run.

So far, we mentioned about SQL Server Agent and the main component definitions and why we need SQL Server Agent. Now, we will talk about the main idea of this article. This article will cover how to alert or notify database administrator when a job reports a failure. If we are notified about SQL Agent job fails, we can handle the issue as soon as possible and prevent the problem without causing the further problems. When we look at this aspect that we need a job fail notification system. We can use SQL Server built-in functions such as database mail or SQL Agent mail, but this solution will not be effective and will not give many details about job steps errors. So, we will overcome this problem with help SQL Server Reporting Service email subscription. Also, we don t need to write some custom html codes to get well formatted emails.

Now we will create a job with the following query. The created job named is DemoJob and scheduled to run every 10 minutes. Through this SQL Agent job, we can create error for every 10 minutes for our demonstration. After the demonstration drop the DemoJob because it will create unnecessary errors.

After the creation of DemoJob you can see that under the folder.

Note: In this tip, we will use Ed Pollack job alerting procedure who discuss details Reporting and alerting on job failure in SQL Server in this article. This stored procedure logs job steps errors, but we will make some little modifications and adopted this stored procedure for SSRS usage and then we will use it.

The following stored procedure will help us to get error logs of SQL Server Agent.

After all these preparation steps about SQL Server Agent, we need to configure SQL Server Reporting Service email settings.

  • Launch the Reporting Service Configuration Manager
  • Connect the SQL Server Reporting Service and click the E-mail Settings tab
  • Fill the SMTP server credentials and email details to this screen

  • Click Apply

After these steps, we will start to design job error alert report which will be sent by SSRS subscription

  • Select Use a connection embedded in my report then click Build define SQL Server connection settings

  • Click Test Connection and ensure about the connection settings of SQL Server than click OK
  • Right click the Datasets folder and click Add Dataset

  • Select Use a dataset embedded in my report and select the data source which created previous step. Select Query type as Text and paste the query

  • Click Refresh fields and populate the monitor_job_failures_forReportingService stored procedure columns to dataset. Click OK

  • Drag and drop text box to report design panel then click the right click context menu and select Expression

  • Add a table to the design panel and then drag and drop the dataset fields to a table

  • Click to File menu then select Save As

  • Write the reporting service web service URL and file name and in which server you want to publish then click save

  • Open the report server web URL and then click (…) button on the report and select Manage in the context menu

  • Click Subscriptions tab then click +New subscriptions

  • Give a description to New Subscription then click Edit Schedule

  • Create a schedule which looks like the below figure

In this step, we will schedule to subscription for every five minutes, because in the procedure we set the looking parameter to five minutes 1440 second. If you want, you can change this schedule setting, but you need to make the same changing in the procedure.

  • Select Deliver the report to as E-Mail and write an email address of any person who will be notified
  • Click Create Subscription

You will get an email which looks like the figure below

Conclusion

In this article, we created a mail notification for SQL Server Agent failed jobs. We used a stored procedure which helps to log detail error of job steps. Through the SQL Server Reporting Service subscription, we created an email notification. The benefit of this approach is that we don’t need any email settings in SQL Server or SQL Server Agent.

Esat Erkec
Latest posts by Esat Erkec (see all)
168 Views