last_run_duration value looks wrong
-
If I am reading my BOL right, the field in SYSJOBSTEPS is supposed to contain the number of seconds that the last occurrence of the job took to complete. When I query the database directly or with sp_help_job or sp_help_jobstep, that shows a time nearly (but not quite) 3 times as long. Yet when I use EM to browse job history, I see the time in hours, minutes, seconds that is 1/3 (slightly more) of the time. I want to avoid having to go through each job individually, so how can I tell what is really being reported in last_run_duration? Any help is appreciated!
-
I dont think you are interpreting BOL correctly. The exact phrase is:
last_run_duration int Amount of time incurred in the previous execution of the job. This MAY or MAY NOT be seconds, it could be MILLISECONDS.....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Hmmmm, even if that were true, then the values I see in the database (e.g. 33954) don't correspond in any meaningful way with the value shown in job history in EM (3 hours 40 minutes). I sure hope that there's a better answer!
Here is the BOL line from sp_help_jobstep:
last_run_duration Int Duration (in seconds) of the step the last time it ran. Thanks for trying, AJ! HEY I JUST FIGGERED IT OUT! Whe I looked at the numbers above, I realized that, instead of seconds, the INT is stored as HHMMSS. So the BOL doc is just plain wrong!
NEVER MIND!!!
-
Alan,
I don't think that IF BOL states the "Amount of time incurred in the previous execution of the job." (Don't know which version of BOL you are using) How can it be wrong?
I would possibly look at getting a newer version of BOL....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Alan, like AJ Ahrens says you do not have the updated version of Books Online. You can download it from http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp.
You can also see the online version topic for sysjobsteps.
-
OK, My version is the one that came on the CD, which I installed. It differs from the one to which AJ and Chris refer, but I still don't like the documentation. The data type is INT, and the description says "Amount of time incurred in the previous execution of the job".
I am glad I was able to figure it out, but the documentation really ought to be a bit more precise! Thanks for those references, I will attempt to get my local version updated...
-
Well, folks, I hate to burst your bubble, but like any complex set of documentation for a product like SQL, the doc is, on occasion, wrong. This is one of those cases. Although a newbie to SQL, I am a highly experienced DBA in another, competing DBMS, which also gets the doc wrong frequently. I updated to 8.00.02 of BOL, and here is the exact reference cut from the BOL topic "sp_help_jobstep" in the TSQL reference:
last_run_duration Int Duration (in seconds) of the step the last time it ran. Like it or not, AJ, to me it looks just plain wrong. I will submit feedback to Microsoft in the hope that it will be updated some day!
-
SELECT
STUFF(
STUFF(
RIGHT('000000' +
CAST(
[last_run_duration]
AS VARCHAR(6))
, 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration]
FROM
[msdb].[dbo].[sysjobsteps]
INNER JOIN [msdb].[dbo].[sysjobs]
ON [sysjobsteps].[job_id] = [sysjobs].[job_id]
--WHERE [sysjobs].[name] ='??'
--AND [sysjobsteps].[step_id] = ??
Jillian
~ we travel forward, no matter the turns...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply