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 12»»

sysjobhistory run_duration Expand / Collapse
Author
Message
Posted Friday, April 19, 2002 8:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 2:59 PM
Points: 26, Visits: 63
Is this in seconds or ? It is not documented in BOL except for amount of time incurred in execution of a job. Thanks ...




Post #3686
Posted Friday, April 19, 2002 8:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:02 AM
Points: 2,049, Visits: 3,596
From SQL Server 2000 System Table Map (download from MS - search on systbl.chm)

"Elapsed time in the execution of the job or step in HHMMSS format."

Hope this helps.

David


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #32104
Posted Friday, April 19, 2002 8:55 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, November 14, 2014 10:06 AM
Points: 8,370, Visits: 745
It is based on time value sections.

Ex.

run_duration = HH:MM:SS
1 = 00:00:01
335 = 00:03:35
102456 = 10:24:56

Hops this helps.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)



Post #32105
Posted Friday, April 19, 2002 9:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 2:59 PM
Points: 26, Visits: 63
Antares686 Thanks for your assistance on this matter, pochinej from www.sql-scripting.com




Post #32106
Posted Friday, April 19, 2002 10:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 2:59 PM
Points: 26, Visits: 63
DavidB Thanks also, I have the sytbl map and was working sql7 never know if its been changed but seems it has not been, Thanks...




Post #32107
Posted Tuesday, April 7, 2009 8:58 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 6, 2014 11:27 PM
Points: 126, Visits: 505
All,


I hate the formatting myself so thought I would share. This code was "borrowed" from Mike Pearson, thanks Mike...


As you can see it's all in the CASE.



select j.name,
case when h.step_name ='(Job outcome)' then '(Package Total)' else h.step_name end
, h.run_status,
CASE len(h.run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(h.run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(h.run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(h.run_duration,3),1)
+':' + right(h.run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(h.run_duration,4),2)
+':' + right(h.run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(h.run_duration,5),1)
+':' + Left(right(h.run_duration,4),2)
+':' + right(h.run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(h.run_duration,6),2)
+':' + Left(right(h.run_duration,4),2)
+':' + right(h.run_duration,2) as char (8))
END as 'Duration'
,h.run_duration
from dbo.sysjobs j
inner join dbo.sysjobhistory h
on j.job_id=h.job_id
where j.name like '%somejobName%'
order by j.name, h.step_name



Post #692124
Posted Thursday, December 23, 2010 12:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 5:37 AM
Points: 84, Visits: 273
One more solution without case statement found in another forum,

SELECT stuff(stuff(replace(str(run_duration,6,0),' ','0'),3,0,':'),6,0,':') FROM sysJobHistory
Post #1038630
Posted Thursday, October 27, 2011 11:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 29, 2013 5:29 PM
Points: 11, Visits: 577
Just curious in a hypothetical extreme situation but what if a job runs beyond a day like say for example 25 hours (or further more weeks, months, years, etc...)?...would it then still be represented as 25 hours or something like 1 day & 1 hour? Thanks in advance.
Post #1197137
Posted Thursday, October 27, 2011 2:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
Yes, days can be included on the value. You can test this by creating a job that just sits for a day. I have a messy formula to calculate the value.

I like to deal with it in seconds so I use this:
((jh.run_duration/1000000)*86400) + (((jh.run_duration-((jh.run_duration/1000000)*1000000))/10000)*3600) + (((jh.run_duration-((jh.run_duration/10000)*10000))/100)*60) + (jh.run_duration-(jh.run_duration/100)*100)

Where jh is the jobhistory table..

CEWII
Post #1197212
Posted Thursday, October 27, 2011 2:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 29, 2013 5:29 PM
Points: 11, Visits: 577
Thanks Elliott for the quick reply, much appreciated. So the further left/highest value that the run_duration field would contain would always be displayed in hours then?
Post #1197222
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse