December 22, 2008 at 3:12 am
I have a SQL job scheduled to run Mon-Sat starting at 23:45 and ending at 04:45 to run every hour. I expected the job not to run on Sunday morning but it did even though Sunday is not on its schedule!
Is this intended behaviour or a SQL Server bug?
Presumably to get round this I need two schedules, one starting at 23:45 to 23:59 and the other schedule starting at 00:45 to 04:45 for the following day.
December 22, 2008 at 5:52 am
If you start a job at 2345 hrs on a Saturday, anything longer than 15 mins will run into Sunday morning. You could have one job to do Mon-Fri and and separate job to manage the shorter Saturday night execution...
James Howard
December 22, 2008 at 6:40 am
Indeed, but the job took less than 15mins to run so it finished before midnight and then ran again every hour thereafter up until its last run time at 04:45 on Sunday which I didn't expect as it's not scheduled to run on Sunday.
December 22, 2008 at 6:45 am
I see what you mean... other option is you could add a check for the day before deciding whether it runs or not...
If (select DATENAME(dw,getdate()))<>'Sunday'
BEGIN
--its not sunday so run code
END
James Howard
December 22, 2008 at 7:07 am
Yes I think I'll do that (thanks for the advice) but I really wanted to understand the logic behind the scheduler. What I was thinking of doing was setting the job to run Mon-Fri 2345-0445 every hour, so at least in theory it should run on Saturday mornings at 0145, 0245, 0345 & 0445 (odd though it may seem)!!!
December 22, 2008 at 12:01 pm
Is SQL Server smart enough to know if you put in 11:45 pm to 04:45 am, it goes into next day?
Your theory could work, Mon-Fri 2345-0445, if it works on Saturday it'll be quite funny
Or safer way is to do 2 schedules?
Monday-Saturday, one from 0000-0445, one for 2345-0000
December 23, 2008 at 3:41 am
I decided to test the theory and setup a test job to send myself an email every hour from 2300-2200 to run on Mondays only. It's Tuesday today and the job has sent an email every hour from 11pm last night. It's next run time is today on the hour.
So I guess that for a weekly recurring schedule, SQL Server only identifies the day relevant to the start time and as you say, isn't clever enough to work out that the end time has spanned midnight and therefore occurs on the next day. So it will carry on running its recurring cycle regardless until it reaches its end time even though the end time may occur on an unscheduled day.
I think the most easily understood way forward is not to use times that span over midnight and use two schedules. I'll just have to put this down to one of those SQL Server quirks one finds every now and then.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply