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.
