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).
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.
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 ENDSo 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).