Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
 Posted - 2009-09-16 : 03:54:10
|
| Hi Scheduling new jobs!I know that there is a history you can view when jobs run to see how long they took to run, but we clear down the history on a Sunday night.So what I would like to know is, is there a way I can record the time that jobs have taken to run to help me adjust the schedules as the schedules for our jobs are spread right throughout the week at the moment and slotting in new jobs is becoming difficult (I get told - 'Oh we got jobs running then so you can't have that slot').I would like to be able to record in a table how long jobs are taking to run over a period of time so i can shuffle them about to clear up timeslots for other jobs.Is there a way to do this?Thanks |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-09-16 : 04:24:37
|
| select * from msdb..sysjobactivity as ja inner join msdb..sysjobs as j on ja.job_id=j.job_idwhere j.name='job name'MadhivananFailing to plan is Planning to fail |
 |
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2009-09-16 : 05:15:49
|
| Thanks for that madhivanan,I also found this since posting:-SELECT sj.name,sja.run_requested_date,CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) DurationFROM msdb.dbo.sysjobactivity sjaINNER JOIN msdb.dbo.sysjobs sjON sja.job_id = sj.job_idWHERE sja.run_requested_date IS NOT NULLORDER BY sja.run_requested_date desc from here:-http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx |
|
|
|
|
|
|
Tracking how long jobs take to run!