none
Backup Jobs Notification RRS feed

  • Question

  • Quick question to see if there is a better way to do this.

    I have a backup maint plan set up on a SQL server - looks something like  the image below...

    As you can see - when the DB backups fail - I get an email to the helpdesk stating that they failed.

    I was working on the server today and wanted to double check to make sure backups where working for a DB and saw that no DB's had been backed up in almost 6 months (WHOOPS!).  

    When I checked the logs as to why - the error was 

    "the job failed. unable to determine if the owner of job has server access."

    I believe that this was because the user that created the job moved on and his user account was deleted.  Unfortunately - because the job never ran, the notification didnt work either.  So much for alerting when a job fails!  I was able to fix by setting the job owner to SA (not sure if this is a best practice or not?)

    So my question is - whats a better way to monitor backup jobs, maint plans etc to make sure that they are running.  Please dont state the obvious - "Check them frequently manually" - in this day and age - reliable monitoring, alerting should be available.  Is there a better way to write this job to alert on failure, or is there an event in the eventlog viewer I can look for and alert on via splunk?

    Appreciate the advice in advance.


    Tuesday, November 12, 2019 6:25 PM

All replies

  • You need to setup DBMail, operators. And in the job properties, set notification as below.

    if you select "When the job completes", you will get an email for both success and failure.


    Best Regards. Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    Tuesday, November 12, 2019 6:49 PM
  • Gotcha - so the one i set up inside the job only works if the job runs.  The one you stated will trigger if it doesnt run at all?

    Thanks

    John

    Tuesday, November 12, 2019 6:54 PM
  • Yes, it send alerts irrespective of your job success/failure.

    Edit: Misread your question, as mentioned by Tom Phillips..this alert will be pushed only if the job runs whether it is success/failure. But the job will not self start unless you have a schedule assigned to it. Yes, it's a good practice to run all the jobs under SA.

    • Edited by Kris_SQL Tuesday, November 12, 2019 7:55 PM
    Tuesday, November 12, 2019 7:05 PM
  • Technically SQL Agent was never able to start the job, so the job alerts are not applicable.

    Unfortunately, there is nothing built into SQL Server Agent to alert on not able to start a job failures.  The good news is, these errors are rare.  The best practice is to run jobs under "sa" account, instead of users.  So the simplest solution to your problem is to change all jobs to run as "sa".

    https://www.brentozar.com/blitz/jobs-owned-by-user-accounts/

    Tuesday, November 12, 2019 7:36 PM
  • Note that not everyone agrees that sa should own all jobs - and considers it to be a security risk to have it that way. Erland for instance. Some info (buried deep inside) here: http://www.sommarskog.se/grantperm.html

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, November 12, 2019 8:05 PM
    Moderator