Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Agent jobs don't run according to schedule Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 7:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 8:10 AM
Points: 323, Visits: 1,466
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
Post #1404788
Posted Thursday, January 10, 2013 6:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 12:21 PM
Points: 18, Visits: 558
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
Post #1405383
Posted Thursday, January 10, 2013 7:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1405441
Posted Friday, January 11, 2013 7:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 8:10 AM
Points: 323, Visits: 1,466
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
Post #1406020
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse