Why when Cancelling a job, on a step set to send success on failure, does it log notifications?

TamusJRoyce

I have a job as step 1 that cancels itself if a certain condition exists where I don't want the job to run.

IF NOT
   (EXISTS(SELECT 1
           FROM sys.dm_hadr_availability_replica_states AS ars
               INNER JOIN sys.availability_groups AS ag
                   ON ars.group_id = ag.group_id
           WHERE ars.is_local = 1 AND ars.role_desc = 'PRIMARY')
    OR
	NOT EXISTS(SELECT 1
               FROM sys.dm_hadr_availability_replica_states AS ars
                   INNER JOIN sys.availability_groups AS ag
                       ON ars.group_id = ag.group_id
               WHERE ars.is_local = 1)
   )
BEGIN
	DECLARE @CurrentJobId AS uniqueidentifier = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))
	EXEC msdb.dbo.sp_stop_job @job_id = @CurrentJobId

	WHILE(1 = 1)
	BEGIN
		WAITFOR DELAY '00:00:01'
		DECLARE @NoOp AS int = 1
	END
END

So in this case, if the job tries to run on a secondary availability group server, the job will be cancelled. I have a similar query that allows jobs to run only if it is a secondary availability group server. And I use an SSIS package to sync server-level information, including jobs, between availability groups.

This job is set to succeed on failure. But SQL Monitor still gives us notifications on failed or cancelled jobs. It is most-likely a fundamental issue with how SQL Server logs cancelled jobs. But could there be an option to filtered these out? It is super-easy to detect if the job is set to succeed on failure.

SELECT rnq.*
FROM #RedgateNotificationQuery AS rnq
       INNER JOIN [msdb].[dbo].[sysjobsteps] AS sjs
              ON sjs.job_id = rnq.job_id
WHERE shs.on_fail_action <> 1 -- 1 = Send success on failure. So filter these.

I also thought someone might find some interest around syncing server-level info between availability groups, including jobs and SSIS packages. And preventing jobs from running on the secondary, or only running on the secondary, if a condition is found. Possibly based on job name or other metadata.

sources! I want to give credit where due. http://blogs.microsoft.co.il/yaniv_etrogi/2016/12/11/alwayson-availability-groups-and-sql-server-jobs/. All credit goes to Yaniv Etrogi (11/12/2016).

TamusJRoyce

Talking to someone prominent in the SQL World a few days ago. They suggested something to do instead of using "EXEC msdb.dbo.sp_stop_job @job_id = @CurrentJobId".

To actually throw an exception. And then. When the job is set to succeed on failure. The job will succeed.

TamusJRoyce

This is causing as many as 40 emails a day. And 80 or more logged events. That is quite a bit of clutter. And the unnecessary communication causes actual errors to occasionally be lost or ignored.

TamusJRoyce

Or if this is an issue coming from sql server, and filtering will not be possible in Redgate Monitor, could someone elude to what RedGate Monitoring is queuing off of that generates notifications? So I can submit a issue request to Microsoft?