I've been thinking about this a lot recently: Is there a consensus as to how to check SQL Server Agent jobs have run correctly?
- Some advice seems to be alerting the operator on failure and addressing those, but this won't account for jobs that haven't run (say if the agent went down for a time) at all rather than run and failed.
- Some advise to use Job Activity Monitor and check the schedules and statuses, which is better but requires you to connect to every server, which can be arduous in a large estate, and maintain a list of job timings.
- We currently have a list of jobs per server and have an operator set up that mails to a single DBA account. Daily we go through and check each job in the spreadsheet against the notification emails checking failures or absence of a notification.
I feel like there has to be a better solution, but my googling hasn't brought anything up. Does anyone have a better solution for managing the status of agent jobs?
EDIT - I should also add that we IDERA SQLDM to monitor some servers, but don't have the budget to look at all our servers. Also our SCOM implementation is in the hands of our server team and currently getting info out of it isn't easy for us. This makes getting reliable information about the status of the agent hard.