0

I've just upgraded my High Availability environment from SQL 2017 to 2019 version 15.0.4236.7 I have same SQL Agent Jobs on both servers with a initial clause to determine if the job should run (if the server is the primary). This has always worked in the past but I've run into an error. Now, the same job on the secondary server fails if it calls a stored procedure. If the job has an inline select to a table that is read only, the Job runs with no issues but the proc throws an error that "The target database is currently....". It's as if the procedure is read before the execution whereas the in line select is not read till the line is invoked. Here is a simple test that works on 2017 secondary server but not on 2019. Not sure if its a bug or a feature.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
use master; 
Declare @AGName varchar(20)  = 'MY-SQLAGNAME'

declare @ret bit

    SELECT
        @ret = count(AGC.name)
    FROM
     sys.availability_groups_cluster AS AGC
      INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id
      INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id
    WHERE
     ARS.role_desc = 'PRIMARY' and AGC.name = @AGName and RCS.replica_server_name = @@ServerName 


 if @ret = 1
 begin;

 Select 'Shouldt go in here'

 select top 1 * from MyDby.dbo.MyTable -- This works
 
 exec MyDb.dbo.AnyProcedure -- This Bombs 

 end
1
  • Sorry for the dumb question, but @ret=0 when you execute the script on the secondary replica? Aug 9, 2022 at 21:00

1 Answer 1

2

I recommend a change in your process.

  1. Move the Primary check logic out of the procedure into the first step of the job. If the step fails, quit the job reporting success. This way, no other steps are run, and you don't have to repeat the logic.

  2. Instead of querying the DMV, you can use the function sys.fn_hadr_is_primary_replica.

A database can only belong to a single AG. So here's an example of the first step logic. Then, of course, you can also wrap it in a procedure.

IF sys.fn_hadr_is_primary_replica ('MyDb') <> 1
    RAISERROR('Not a primary replica.', 16, 1)
2
  • This worked. -> IF sys.fn_hadr_is_primary_replica ('MyDb') = 1 begin exec MyDb.dbo.AnyProcedure end
    – Tomasz
    Aug 10, 2022 at 11:51
  • Hmm this is still an issue if I declare a variable anywhere inside the job IF sys.fn_hadr_is_primary_replica (N'MyDb') = 1 begin declare @int int set @int = 0 exec MyDb.dbo.MyStoredProcedure end -> This Fails
    – Tomasz
    Aug 18, 2022 at 15:07

Your Answer

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

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