We recently upgraded from 2008 to 2012, with one of our applications running on an active & passive server (Always On). It has been running SQL Server Agent jobs twice at the exact same time.

We verified it wasn't the passive box, but we can't explain the trigger or reason(s) as to why its running twice.

Only one scheduled agent job appears. It is setup correctly to run 9:15 for Mon & Thu:

Occurs every week on Monday, Thursday at 9:15:00 AM. Schedule will be used starting on 10/9/2014.

By disabling the scheduled job running the SP on the passive server the day before, only the "active" machine was scheduled and able to run. Only the active machine ran and the results were correct: one email was sent, it was completed and the records were not duplicated; however, when we run the job manually through the scheduler it fails since the server is passive.

Any insight?

  • 3
    Have you checked for more than one schedule on the agent job? – Cody Konior Jun 7 '16 at 15:22

I have working SQL to determine which machine is the PRIMARY or SECONDARY server and to use within the Stored Procedure scheduled to run on both machines:

from sys.dm_hadr_availability_replica_states ars
inner join sys.availability_groups ag
on ars.group_id = ag.group_id

Note: I was expecting the database to be identified but instead all the databases are placed into an "availability group (AG)".

Results from the ACTIVE & PRIMARY machine 
note: whichever says "1" for is_local and "PRIMARY" indicated the active machine
role_desc   name        is_local
PRIMARY     MasterSQL2AG    1
Results from the ACTIVE & PRIMARY machine 
note: whichever says "1" for is_local and "SECONDARY" indicated the PASSIVE machine 
role_desc   name        is_local

Another query providing more details and for the specific DBID is this:

DECLARE @description VARCHAR(25);
FROM   master.sys.DATABASES d
INNER JOIN master.sys.dm_hadr_availability_replica_states hars 
    ON d.replica_id = hars.replica_id
WHERE database_id = DB_ID('Customers')

Useful references, links and examples:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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