SQLAgent executing command on Mirror database

Question

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!

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

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.