locked
SQL Agent jobs operate across mirror RRS feed

  • Question

  • SQL Server 2005 SP2 CU3 (Build 3186)

     

    We have a SQL Agent job which runs on a server which is running mirrored databases. If the database which is affected by the job is in the mirror state, SQL Server agent locates the principle, executes the statements against that, and completes successfully.

     

    This behavior is not consistent.

     

    This is potentially serious; if jobs implemented on both sides of a mirror are not disabled, they may both execute.

     

    We have suffered incorrect financial values due to a reconciliation job running twice (on each side of the mirror, and updating the principle twice).

     

    It's easy to set up a test job to confirm this.

     

    Can anyone else re-create this?

     

    Thanks!

     

     

     

     

     

     

     

     

    Wednesday, October 17, 2007 2:02 PM

All replies

  • No I have not seen this. Is it a batch job, that does data modification? Why are you scheduling it on the mirror?

    With these things I normally set them up (on the mirror) so that they first check whether the database is online and read-write, and only execute against it if it is - but it sounds like your issue is worse than that.

    Thursday, October 18, 2007 1:00 PM
  •  

    "With these things I normally set them up (on the mirror) so that they first check whether the database is online and read-write, and only execute against it if it is....."

     

    Could you provide an example of this? I need to do this very task.

     

    Do I also need the same logic on the principal?

     

    Thanks

    Tuesday, November 13, 2007 8:07 AM
  • To get round the problem I've set up a check. This is the method I use - the local variable is set to 2 if the database is principle and 1 if the database is mirror.

     

    declare @is_db_principle bit

     

    set @is_db_principle  = (select mirroring_role from msdb.sys.database_mirroring where database_id = db_id(yourdabatasename'))

     

    --Then enable or disable jobs according to the above

     

    if @is_db_principle = 1

    msdb.dbo.sp_update_job @job_name = 'yourjobname', @enabled = 0

    else

    msdb.dbo.sp_update_job @job_name = 'yourjobname', @enabled = 1

     

    Hope this helps,

     

    Nick

     

     

     

     

    • Proposed as answer by DaveAce Wednesday, October 14, 2009 1:20 PM
    Tuesday, November 13, 2007 8:32 AM
  • Handy piece of code..thanks...but I needed to change @is_db_principle bit type to int for it to work correctly

    HTH

    Dave

    Monday, March 3, 2008 3:18 AM
  • This DOES happen, and is in fact totally consistent and repeatable behaviour.

    If the scheduled job in question has the mirrored database referenced in the job step definition, then the SQL agent WILL ALWAYS resolve the mirror relationship when it runs.

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step_name', 
    		@step_id=1, 
    		@cmdexec_success_code=0, 
    		@on_success_action=1, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'TSQL', 
    		@command=N'exec Database..procname @DBName =''MirroredDB''', 
    		@database_name=N'MirroredDB', 
    		@flags=0
    
    

    If the job is set to run on the "master" database and uses a full 3 part name to execute the job in the correct database, then the agent WILL NOT resolve the mirror and the job will fail if the database is the Mirror in the partner.

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step_name', 
    		@step_id=1, 
    		@cmdexec_success_code=0, 
    		@on_success_action=1, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'TSQL', 
    		@command=N'exec Database..procname @DBName =''MirroredDB''', 
    		@database_name=N'master', 
    		@flags=0
    
    

    So far I've found this behaviour ro be totally consistent and repeatable, but also TOTALLY UNDOCUMENTED.

    • Proposed as answer by Andeavour Thursday, November 24, 2011 9:46 AM
    Thursday, November 24, 2011 9:46 AM