SQL jobs interfering with eachother

  • I have 2 sql jobs running daily (nightly) on the server. These are scheduled 1:30 hr/min  apart.
    Both these jobs have to be run one after the other and they execute ssis packages to DELETE, INSERT and UPDATE multiple tables in different databases.

    My question is :
    1. When i schedule only one of the two jobs ( any) it completes in its stipulated time; But when I schedule both the jobs to run; Both the jobs get delayed.

    Can anyone explain the resource that are held on the server level by sql agent when scheduling the jobs ?

    Any help/ direction is appreciated

    T

  • Could you not work it that final step of the first job calls the second job.  Then they won't bump into each other?

  • mark.humphreys - Thursday, September 6, 2018 3:10 AM

    Could you not work it that final step of the first job calls the second job.  Then they won't bump into each other?

    Not sure what you meant... How to wait for starting say job B after completing Job A. please help

  • You say that one job runs after another, therefore you could add a final step on the first job which calls the next job you want to run after the previous one.

    I have overnight jobs that run in a sequence and start after the previous job completes.  The final step uses this command.

    exec msdb.dbo.sp_start_job @job_name = 'Your Job Name Here'

  • mark.humphreys - Thursday, September 6, 2018 3:38 AM

    You say that one job runs after another, therefore you could add a final step on the first job which calls the next job you want to run after the previous one.

    I have overnight jobs that run in a sequence and start after the previous job completes.  The final step uses this command.

    exec msdb.dbo.sp_start_job @job_name = 'Your Job Name Here'

    Thanks for this. there is a time gap of  1 hour between the jobs.

  • Using the method above there doesn't need to be a gap.  You can just call job b as soon as job a finishes.

    Unless there is a valid reason for the gap.

    Hopefully that all makes sense.

  • TJDBA - Thursday, September 6, 2018 3:59 AM

    mark.humphreys - Thursday, September 6, 2018 3:38 AM

    You say that one job runs after another, therefore you could add a final step on the first job which calls the next job you want to run after the previous one.

    I have overnight jobs that run in a sequence and start after the previous job completes.  The final step uses this command.

    exec msdb.dbo.sp_start_job @job_name = 'Your Job Name Here'

    Thanks for this. there is a time gap of  1 hour between the jobs.

    You could add a step at before the step that calls the second job to wait for 1 hour
    WAITFOR DELAY '01:00:00'

  • TJDBA - Thursday, September 6, 2018 3:59 AM

    mark.humphreys - Thursday, September 6, 2018 3:38 AM

    You say that one job runs after another, therefore you could add a final step on the first job which calls the next job you want to run after the previous one.

    I have overnight jobs that run in a sequence and start after the previous job completes.  The final step uses this command.

    exec msdb.dbo.sp_start_job @job_name = 'Your Job Name Here'

    Thanks for this. there is a time gap of  1 hour between the jobs.

    You may have to rebuild stats between the jobs or you may have to do a recompile for the procs in the second job.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • TJDBA - Thursday, September 6, 2018 3:59 AM

    mark.humphreys - Thursday, September 6, 2018 3:38 AM

    You say that one job runs after another, therefore you could add a final step on the first job which calls the next job you want to run after the previous one.

    I have overnight jobs that run in a sequence and start after the previous job completes.  The final step uses this command.

    exec msdb.dbo.sp_start_job @job_name = 'Your Job Name Here'

    Thanks for this. there is a time gap of  1 hour between the jobs.

    When you say there is a gap of 1 hour between jobs, do you mean that job completes before job 2 starts?  Or that job 2 simply starts one hour after job 1 starts?

  • Add a new step at the end of the first job:
    exec msdb.[dbo].[sp_start_job]

    Parameters:
    @job_name  sysname    = NULL,
    @job_id  UNIQUEIDENTIFIER = NULL,
    @error_flag INT     = 1,  -- Set to 0 to suppress the error from sp_sqlagent_notify if SQLServerAgent is not running
    @server_name sysname    = NULL, -- The specific target server to start the [multi-server] job on
    @step_name sysname    = NULL, -- The name of the job step to start execution with [for use with a local job only]
    @output_flag INT     = 1  -- Set to 0 to suppress the success message

    Make it call the job No.2 and remove/disable any schedule created for that job.

    _____________
    Code for TallyGenerator

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply