I have 2 nodes in an AG. Non-readable secondary, sync commit mode.

There are sql agent jobs setup on both machines. The schedules are turned off on secondary so those jobs wont run. If there is failover then we manually enable the schedules for jobs on secondary.

The schedules run various jobs almost every minute.

When the primary goes down (technically because of network issue), but the machine is running, how do I immediately prevent the scheduled sql agent jobs on primary from running?

  • "how do I immediately prevent the scheduled sql agent jobs from running?" - to clarify, you mean on the primary replica that no longer has network access?
    – J.D.
    May 6, 2022 at 11:54
  • 1
    Yes I have updated now in question as well.
    – variable
    May 6, 2022 at 12:23

3 Answers 3


Wrap T-SQL of your jobs into this code:

if  ((select primary_replica from sys.dm_hadr_availability_group_states) = @@SERVERNAME
    and (select primary_recovery_health_desc from sys.dm_hadr_availability_group_states)  = 'ONLINE'
    and (select synchronization_health_desc from sys.dm_hadr_availability_group_states)  = 'HEALTHY')
    <T-SQL here>

This way T-SQL will only run if SQL Server is in primary role, online and synchronization is healthy

See the https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-availability-group-states-transact-sql?view=sql-server-ver16

  • 1
    You should also have an exact copy of those jobs on the secondary so if there is a failover, the job will start running from the new primary. May 6, 2022 at 14:49
  • @DominiqueBoucher absolutely May 6, 2022 at 16:16
  • Isn't sys.fn_hadr_is_primary_replica more appropriate for the check?
    – variable
    Jul 16, 2022 at 16:36
  • @variable sys.fn_hadr_is_primary_replica only shows 1 or 0, while sys.dm_hadr_availability_group_states shows addition information related to state and sync status (online, healthy) Jul 18, 2022 at 12:43
  • If you could give the official documentation link for this then I will mark as answer
    – variable
    Jul 18, 2022 at 12:48

You can use the built in system function inside the job step to check if the replica is primary.


If 1 is returned, do something, else exit the job.

IF COALESCE(sys.fn_hadr_is_primary_replica(‘<dn_name>’),1) = 1
    Do something…

COALESCE accounts for the function returning NULL, which will be the case if the database passed to the function is not part of an availability group.


We keep our job definitions same across all the nodes in the AG (Primary and Secondary's) including their schedules. But the only thing we add to our job's is one step Test write status before actual job step.

The Test write status does this:

IF (DATABASEPROPERTYEX('ag_database', 'Updateability') <> 'READ_WRITE')
    THROW 51000, 'This is not a writeable replica', 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.