Alwayson SQL Agent Job must be executed only on PRIMARY

Question

Post reply

Alwayson SQL Agent Job must be executed only on PRIMARY

vsamantha35

SSChampion

Points: 11551
More actions
March 18, 2023 at 9:55 pm

#4162379
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 = '<AG_Name>'
and ars.is_local = 1) = 'PRIMARY'
BEGIN
         UPDATE STATISTICS <databaseName>.dbo.<table_name> WITH FULLSCAN, COLUMNS
END
ELSE
BEGIN
          RETURN;
END
```
/*
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.
*/
Thanks,
Sam

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

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

Emperor100 SSCrazy Points: 2173 More actions · Answer 1

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?

=======================================================================

vsamantha35 SSChampion Points: 11551 More actions · Answer 2

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?

Emperor100 SSCrazy Points: 2173 More actions · Answer 3

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

=======================================================================

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 4

Hi,

you can try a Job with 2 steps.

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

good luck,

kind regards,

Andreas

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

There is a function available - use that instead:

Use <database>;
Go

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

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

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') )
begin
     print 'OK'
end
else
begin
RAISERROR('DB <DMdb> is MIRROR', 16, -1);
end

Johan

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