You can write this more tersely (e.g. without any variables at all), but I thought logically breaking it up might be more helpful.
DECLARE @Today DATE = SYSDATETIME();
DECLARE @FirstDayNextMonth DATE = DATEADD(MONTH, 1,
DATEADD(DAY, 1-DAY(@Today), @Today));
DECLARE @LastWorkDayThisMonth DATE;
SELECT @LastWorkDayThisMonth = MAX(CalendarDate)
FROM dbo.CalendarView -- guessing on name here
WHERE WorkDay = 1
AND CalendarDate >= @Today
AND CalendarDate < @FirstDayNextMonth;
IF @LastWorkDayThisMonth = @Today
BEGIN
-- do your last work day of the month stuff
END
(Also, I suggest changing your calendar view to expose DATE
and not DATETIME
.)
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.
Best Answer
To schedule a job to run once a month, in this case midnight on the first day: