How do you use cloud databases? Take the survey.
Options
Can't SQL Monitor distinguish between a job failing and a job being stopped?
LyleG
Posts: 2 Bronze 1
I have several SQL Agent Jobs that need to run on both replicas of an availability group. The first step of these jobs checks to see if it is primary, and if not, it stops itself. SQL Monitor generates a "Job Failing" alert for these even though the job outcome message clearly states "The job was stopped".
Tagged:
Answers
For Job Failing, we poll the msdb.sysjobhistory table for job outcomes where the run_status field is 0, which corresponds to job failing.
When a job is stopped the exit code we see is the same as for cancelled (3), Microsoft don't provide an exit code for Stopped. See run_status here: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql.
For any SQL version >= 2008 the run_status should be 3 if the job is stopped. If the run status is 3, indicating cancelled, we (should) completely ignore the job. Are you seeing run_status 3 jobs being reported?
Yes, run_status is 3.
Hopefully it fits the bill.