May 6, 2009 at 1:39 pm
This is a general question.
I have a job(includes 18 job steps) which is sheduled to run every day at 7pm, its a very big job which runs approx 20hrs roughly. Duration varies many times sometimes it also takes 21 hrs.
What happens to my job shedule for today if the job which started yesterday is still running through todays shedule, does it just fail and miss todays shedule?
If my job is running at 15th step in yesterdays shedule, can my todays shedule start running at 1st step simultaneously.
am i asking funny questions 😉
May 6, 2009 at 3:36 pm
Tara,
this is a great question.
Microsoft Website says about SQL Server 2005:
A SQL Server instance can only run one instance of a job at a time. If a job has started according to its schedule, you cannot start another instance of that job on the same server until the scheduled job has completed. In multiserver environments, every target server can run one instance of the same job simultaneously.
http://msdn.microsoft.com/en-us/library/ms190741(SQL.90).aspx
You can actually test if you create a job that runs 2 min but schedule it to run every min and check how many instances of the job is running. You can do it many different ways, let us know if you need more help on this.
Regards,Yelena Varsha
May 6, 2009 at 6:00 pm
If the job runs over the start time, it will miss the schedule for the next day. I have seen this behavior for transaction log backups. Let's say you have your TLog backups set to run every 15 minutes. If a backup kicks off at 1:00 and takes 16 minutes to run, the next backup will run at 1:30 (or the next time it is scheduled to run). In your case, that would be the next day.
The only way I can think of for you to be able to run the job simultaneously is to script the job and create a duplicate job. Schedule job 1 to run Mon, Wed, Fri and schedule job 2 to run Sun, Tues, Thurs, and Sat. That seems a little extreme though because your job would almost be running 24 hours a day, and in some instances two at the same time.
May 6, 2009 at 8:35 pm
The job definitely gets missed if its still running during its next scheduled run.
You could also check whether any blocks occur that delay that job.
May 7, 2009 at 9:11 am
I will test if retry attempts will work in this situation
Regards,Yelena Varsha
May 7, 2009 at 9:20 am
If job is started by the instance we can't start the same job with another instance.
May 7, 2009 at 11:07 am
I did a nice little experiment:
I scheduled a job to run every 5 min when the job duration is 10 min with retry attempts every 3 min:
Job JobConcurencyTest2
Job Step1
Type; TSQL
WAITFOR DELAY '00:10:00'
Retry Attempts 7
Retry Interval 3 Minutes
Otput File C:\z_WaitForLog.txt
Schedule: Running every 5 min starting 12:20 PM
The results: the job starts every 15 min - 10 min to run plus the schedule try to start every 5 min
ob 'JobConcurencyTest2' : Step 1, 'Step1' : Began Executing 2009-05-07 12:20:30
Job 'JobConcurencyTest2' : Step 1, 'Step1' : Began Executing 2009-05-07 12:35:30
Job 'JobConcurencyTest2' : Step 1, 'Step1' : Began Executing 2009-05-07 12:50:30
Job 'JobConcurencyTest2' : Step 1, 'Step1' : Began Executing 2009-05-07 13:05:30
That means that the retry attempts do not work. Otherwise the job would start every 13 min (10 + 3) not every 15 min. Obviously for the retry attempts to be considered, the job has to start first....(???)
Regards,Yelena Varsha
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply