Alwayson SQL Agent Job must be executed only on PRIMARY

  • Hi All,

    We have an SQL 2017 Always on AG in our environment. We have few update stats job which we wanted to run only on PRIMARY replica. we create the jobs on both replicas but it should execute only on PRIMARY replica. For that, we are trying to use this logic. Not sure, why it is throwing error. Any advise.

    --check if it is a PRIMARY Replica or not
    IF (SELECT ars.role_desc from sys.dm_hadr_availability_replica_states ars inner join sys.availability_groups ag on ars.group_id = ag.group_id
    where = '<AG_Name>'
    and ars.is_local = 1) = 'PRIMARY'
    UPDATE STATISTICS <databaseName>.dbo.<table_name> WITH FULLSCAN, COLUMNS


    Msg 976, Level 14, State 1, Line 7

    The target database, 'databaseName', is participating in an availability group and is currently not accessible for queries.

    Either data movement is suspended or the availability replica is not enabled for read access.

    To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.




  • Can you check if the data movement is in operation and AG is functional between Primary and Secondary nodes?

    Have your tried running this agent job earlier?


  • Data movement will be always there as it is synchronous commit replica and this logic works for doing index rebuilds without any issue. But for update stats why it is throwing this error, I dont understand. We are saying only if it is PRIMARY , then get inside and perform the UPDATE stats. not sure, why?

  • Is this happening when you are trying to connect via listener ? Can you please add the output of select @@servername after connecting thru listener?


  • Hi,

    you can try a Job with 2 steps.

    1. step will check is sql server is the primary part of the aoag
    2. step will only execute if the sql server is the primary part of the aoag.

    good luck,

    kind regards,


  • There is a function available - use that instead:

    Use <database>;

    If sys.fn_hadr_is_primary_replica()) <> 1
    Raiserror('%s is not the primary replica - exiting...', -1, -1, @@servername) With NoWait;
    Update Statistics dbo.TableName With Fullscan, Columns;


    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I add an extra jobstep to check the db role!

    This step will quit reporting success when the raiserror is executed !

    It will proceed to the next step if this step does not fail.


    if exists ( select  1 
    from sys.database_mirroring
    Where mirroring_role_desc = 'Principal'
    and database_id = db_id('DMdb') )
    print 'OK'
    RAISERROR('DB <DMdb> is MIRROR', 16, -1);


    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply