0

I have a mirrored database DB1, ServerA as primary and ServerB is mirror.

I have a job running on ServerB (mirror), configured to run against DB1 database.

I was expecting the job to fail but the job connects to ServerA, completes successfully. I am not able to reproduce the scenario on a different mirrored instance.

I am curious to know when this happens (SQL Agent connects to a different Server if the database is in mirror/restoring mode).

Note: If I re-configure the job to run against master database and access DB1 database using db1.schema.table it fails!

1

The easiest way to prevent the job from running on the mirror (remotely executes on the principal) is add as a first step a USE statement for YourDBName e.g.

enter image description here

enter image description here

The Job Step will Fail on the Mirror and the Job History Log Entry will resemble the following:

The database "YourDBName" cannot be opened. It is acting as a mirror database. [SQLSTATE 42000] (Error 954). The step failed.

I am using SQL server 2012 SP4 Build No 11.0.7469.6

I understand that initially the database jobs will run on the Principal Only, however after a Mirror Failover they also appear to execute on the Mirror also, but actually will be remotely executing on the Principal, even though this does not appear to make much sense.

| 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.