I have several agent jobs that run on different schedules.
Job 1 runs every 5 minutes, only executing a stored procedure that usually takes less than 30 seconds to run.
This job Selects from table "users" then uses a cursor to loop through the results and insert records into local tables. After the cursor finishes 4 batch calls are made to a remote linked server to insert the records from the local tables to remote tables. Then the stored procedure ends.
The stored procedure sql is wrapped in a Begin, Begin Try, End Try, End statement.
Job 2, runs every hour, it too only executes a stored procedure, however, this job can take 20 minutes to run.
Job 2 performs two different Selects both referencing the "users" table (which Job 1 also selects from) and inserts the results into local tables, but without using a cursor. Then 4 batch calls to a remote linked server to insert the records from the local tables to remote tables. This stored procedure is also wrapped in a Begin, Begin Try, End Try, End.
When I check the Job Activity Monitor log for Job 1, it shows the duration is consistently under 30 seconds, except for the time that it runs on the hour, with Job 2. At this time it shows the duration 20+ minutes - I guess as a result of Job 2, the long running job, running.
Does this mean that Job 1 begins and as it begins Job 2 starts causing Job 1 to not complete until after Job 2 finishes (the reason why SQL agent log shows the duration of Job 1 as 20 minutes, even though this job never takes 20 minutes to actually execute)?
Is there a way that I can keep this long running job isolated so that it doesn't cause other fast running jobs to be delayed?