Agent jobs don't run according to schedule

Question

Agent jobs don't run according to schedule

ken.trock

SSCertifiable

Points: 5147
More actions
January 9, 2013 at 7:37 am

#269085

Has anyone seen in SQL 2005 SP3 where agent jobs simply don't abide by a schedule setup? I had a bunch of jobs that needed to kickoff an hour longer so I scripted it.
UPDATE s SET active_end_time = active_end_time + 10000--Add an hour
FROM msdb..sysschedules s
INNER JOIN msdb..sysjobschedules js ON js.schedule_id = s.schedule_id
INNER JOIN msdb..sysjobs j ON j.job_id = js.job_id
WHERE j.name LIKE 'This_Job_Needs_Another_Hour%' AND j.enabled = 1
I made sure a couple look right in the GUI scheduler. They run every 15 minutes and they're definitely not taking longer than that (which would prohibit the job from running on it's next interval). The SQL Agent logs are clear.
The best I can tell is that the agent service should be bounced.
Thanks,
Ken

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

telcogod SSC Enthusiast Points: 143 More actions · Answer 1

HI,

It may be agent SQL Server Agent Job History Log limit is being breached (default is 1000 rows) you can look at msdb..sysjobservers for last run date and time.

HTH - Walter

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

ken.trock (1/9/2013)
Has anyone seen in SQL 2005 SP3 where agent jobs simply don't abide by a schedule setup? I had a bunch of jobs that needed to kickoff an hour longer so I scripted it.
UPDATE s SET active_end_time = active_end_time + 10000--Add an hour
FROM msdb..sysschedules s
INNER JOIN msdb..sysjobschedules js ON js.schedule_id = s.schedule_id
INNER JOIN msdb..sysjobs j ON j.job_id = js.job_id
WHERE j.name LIKE 'This_Job_Needs_Another_Hour%' AND j.enabled = 1
I made sure a couple look right in the GUI scheduler. They run every 15 minutes and they're definitely not taking longer than that (which would prohibit the job from running on it's next interval). The SQL Agent logs are clear.
The best I can tell is that the agent service should be bounced.
Thanks,
Ken

I believe that it's a bit more complicated than that. You have to change the schedule for the job using the built in procs which will also change the "next run time" in the job table, etc, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ken.trock SSCertifiable Points: 5147 More actions · Answer 3

Thanks guys. It looks like having our server support team recycle the SQL Agent service did the trick. All jobs are running through their entire schedules.

Ken