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?
Best Answer
An easy workaround solution would be to nudge either of the jobs to start earlier/later and to not run together with the other job.
E.g.
It's not an issue with the job scheduler, but an issue with the underlying tables that are being accessed. When Job 2 starts, it manages to acquire a lock on the users table (possibly: IX, IU) before the Job 1 is able to acquire its required locks on the sameusers
table. Then Job 1 has to wait.You are encountering blocking as previously observed by @MaxVernon. You could run the following script at the full hour to see if you can catch the statements involved:
The output will show statements that are blocking or blocked. You will probably find that the statements are from the Job 1 and Job 2.
Possible solutions: