December 21, 2001 at 7:22 pm
Hi,
I've set up an automation to allow a user to set up some simple data validation jobs from a web site. It works by creating a step for each validation. When the job is started, SQL records the start time, which I retrieve through sp_help job; but the sp_help_job doesn't have a job_duration function, so I set up the last step of every job to write to a table that tells me what time the job ended (or, what time the last step was finished anyway). Then, I decided that I wanted to show how long each job step took, so I used sp_help_jobstep to get the last_run_duration, which records in seconds how long each jobstep took.
Then, as I loop through the jobsteps to write them out to the screen, I sum the last_run_durations. The sum of all jobstep durations reports 12 hours 8 minutes and 57 seconds, but I know the job only took 8 hours, based on hearing my server run and taking the endtime, recorded in my db table minus the start time recorded in sp_help_job (obviously, there are some performance issues here I'm working on), but the question is: what's happening with the jobstep duration. I thought that a new jobstep didn't get called until the one before it had completed.
I have calculated the math out by hand as well, so while I do make many programming errors, I don't tend to think it's me this time.
If you've encountered this problem before, let me know what's going on. Also, why doesn't Microsoft track how long the entire job takes? It seems like a critical component of the equation.
Thanks beaucoup!
April
December 21, 2001 at 10:12 pm
Wow!!! sounds like a lot of work.
I'm not sure of the problem and I guess MS thinks that adding up all the step times should be the total time.
Personally, when I want to track this I mark the start and end times in a table using the first and last steps. If you want intermediate steps, then you can mark them as well whereever you want.
Steve Jones
December 21, 2001 at 11:00 pm
It is a ton of work...the reason I was using the sp_help_jobstep is because I actually wanted to get the duration without calculating it (or storing it, since the msdb database is already doing this), I could use datediff, as I do have a table that tracks all the jobsteps. In retrospect, it seems that would have been a better solution. I doubt that many SQL gurus have ever bothered to test this. Why would a sane person, or at least a more experienced one?
April, The Newbie
🙂
December 22, 2001 at 6:34 pm
I've checked the job logging against what actually happens at it did seem to be about right - but I haven't done it for a while and not for anything running this length of time. This was just an excercise as I probably wouldn't rely on it anyway. 8 hours to 12 hours is a big difference - but not big enough to think that something is dramatically wrong. Try logging start and end of steps (send an email is easy) to check the individual figures.
Cursors never.
DTS - only when needed and never to control.
December 24, 2001 at 11:26 am
I agree with Nigel. Start logging the steps and then you can find where the issue is.
Steve Jones
December 24, 2001 at 11:30 am
Thanks,
Sounds like a good idea. I'll try it in the coming days and let you know what I find out.
Happy Holidays!
April
December 26, 2001 at 2:13 am
Hi,
I found the problem. The sp_help_jobstep documentation says that last_run_duration is an int expressed as: "Duration (in seconds) of the step the last time it ran." In reality last_run_duration is expressed in HHMMSS.
A little gift from the elves.
Thanks.
April
December 26, 2001 at 8:16 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply