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.
Job 1 Schedule: 00:14, 00:29, 00:44, 00:59, 01:14, ....
Job 2 Schedule: 01:00, .......
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 same
users 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:
SELECT sp.spid,
sp.kpid,
sp.blocked,
sp.hostprocess,
sp.waittype,
sp.lastwaittype,
DB_NAME(sp.dbid),
sp.cpu,
sp.physical_io,
sp.memusage,
CASE
WHEN sp.blocked = 0 THEN sp.spid
WHEN sp.blocked != 0 THEN blocked
END AS chain,
dest.text
FROM sys.sysprocesses AS sp
OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS dest
WHERE spid > 50
AND spid <> @@spid
AND (
spid IN (SELECT blocked
FROM sys.sysprocesses
WHERE blocked != 0
AND spid > 50
AND spid != @@spid)
OR blocked != 0
)
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:
- Adding a NOLOCK hint can help, but should be avoided if possible.
- Then you could also consider optimising the code so that the steps in Job 2 run faster.
- Changing the schedules of the Job 1 and/or Job 2.
We noticed similar behavior few times within a year on one of the production server with SQL Server 2014 SP2 installed.
Symptom: All SQL Agent jobs that run SSIS packages suddenly hanged in running state, while no real activity on data engine side.
Resolution: Both times this issue was resolved by pushing all pending windows updated patches to be installed. After this SQL Agent was restarted and jobs behavior was normal again.
Seems the issue is somehow related to .NET stack on a server and OS that we obtain via WSUS
Best Answer
This can be achieved using Frequency part of Scheduler.
You may create three jobs schedule, For Job1 as below:
Same thing will be repeated for Job2 and Job3 however you need to change start date to 16th and 23rd January in this part:
You may also use Past dated by changing date from the above calendar. After the job has executed, SQL Server will calculate a Next Execution date and time. So if you simply revert the system clock then it will be waiting for the next scheduled execution, it won't work that way. Better to schedule from current date and onwards.
Hope this helps.