Scheduled jobs run twice after upgrade to SQL Server 2012

Question

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?

score -1 · Answer 1 · edited Apr 13 '17 at 12:42

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:

select
    ars.role_desc,ag.name,ars.is_local
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
--------------------------------------------------------------------    
IP:  100.1.1.1    
role_desc   name        is_local
PRIMARY     MasterSQL2AG    1
SECONDARY   MasterSQL2AG    0

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

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

DECLARE @description VARCHAR(25);
SELECT *
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:

Scheduled jobs run twice after upgrade to SQL Server 2012

1 Answers1