SQL jobs interfering with eachother

Question

Post reply

SQL jobs interfering with eachother

TJDBA

Default port

Points: 1490
More actions
September 6, 2018 at 3:01 am

#362437

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

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

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

mark.humphreys SSC Eights! Points: 867 More actions · Answer 1

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

TJDBA Default port Points: 1490 More actions · Answer 2

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

mark.humphreys SSC Eights! Points: 867 More actions · Answer 3

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'

TJDBA Default port Points: 1490 More actions · Answer 4

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.

mark.humphreys SSC Eights! Points: 867 More actions · Answer 5

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.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 6

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'

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

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)

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 8

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?

Sergiy SSC Guru Points: 110208 More actions · Answer 9

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