It is Tuesday!! Time for Part 3 in the series about SQL Server Agent Job Failures. This installment will show you how to setup SQL Agent Alerts and Operators for sending notifications when Job Failures occur.
First step:
You must already have Database Mail configured on your SQL Server in order to follow along with these steps. Check out Steps 1 – 3 in my blog post to help you get Database Mail configured. Once you have that complete, you are ready to get started.
Let’s take a look at one of our current SQL Server Agent Jobs.
Get-DbaAgentJob -SqlInstance "localhostsql2017" -Job "!dbatools - Update Module Daily"
Our example shows that currently there is no OperatorToEmail value configured. So, if this job fails no one will know unless they are looking at the SQL Server Agent Activity Monitor.
Second step:
Now you will see how to identify what SQL Agent Operators are configured on your SQL Server. This will allow you to configure the SQL Server Agent Job to use to send failure notifications.
Get-DbaAgentOperator -SqlInstance "localhostsql2017"
Here are the results of running the
Get-DbaAgentOperator command to display a list of SQL Agent Operators already configured on your system. The DBA Operator is perfect for using for sending SQL Agent Job Failures.
Third Step:
Now that you have a SQL Agent Job and SQL Agent Operator identified you will assign the Operator to the Job. After that you will run a test to make sure the SQL Agent Failure sends the notification as we expect.
Set-DbaAgentJob -SqlInstance "localhostsql2017" -Job "!dbatools - Update Module Daily" -EmailOperator "DBA" -EmailLevel OnFailure
Just like that we have now set a SQL Agent Operator to this SQL Agent Job to notify the DBA team when a SQL Agent Job Failure occurs for this job. You can also look in SSMS to verify the changes took place as expected.
Final Step:
You can now check that notifications will be sent as expected upon failure of this particular job. I have placed a booby trap in the job to cause it to fail and trigger our notification.
Start-DbaAgentJob -SqlInstance "localhostsql2017" -Job "!dbatools - Update Module Daily"
Conclusion:
And just like that you have been notified that this SQL Agent Job Failed during execution.
Now get to work and figure out why the job failed!!!
Resources:
Part 1: Checking remote servers for failed jobs from a Central Console
Part 2: Generating HTML report for failed jobs and Sending E-Mail
Configuring SQL Server Database Mail
Watch for Part 4 and the final post in the series where you will learn how to send SQL Server Agent Failures information to a database.
If you found this blog handy, please use the Subscribe section at the top of the screen to join my newly created mailing list. I hope to start delivering content via the mailing list soon. 
The post Failing SQL Agent Jobs – Part 3 appeared first on GarryBargsley.com.