Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kyle Neier » Working with SQL Agent Durations
Add to briefcase
17 posts, Page 1 of 2
12»»

Working with SQL Agent Durations Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Kyle Neier ,
Posted Thursday, August 23, 2012 12:02 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:52 AM
Points: 194, Visits: 1,059
Comments posted to this topic are about the item Working with SQL Agent Durations
Post #1348872
Marc Kuppens
Posted Thursday, August 23, 2012 5:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 22, 2013 7:56 AM
Points: 30, Visits: 287
Hi Kyle,
at the end of your article you state : "There is not an equivalent to Truncate in T-SQL"
Maybe I missed something but what's wrong with the round function (with the third argument <> 0)?

try :
declare @t1 decimal
set @t1 = 23456
select 'using round(?,0) ', round((@t1 / 100),0) as [23456/100], round((-@t1 / 100),0,0) as [-23456/100]
union all
select 'using round(?,0,1)', round((@t1 / 100),0,1)as [23456/100], round((-@t1 / 100),0,1)as [-23456/100]
union all
select 'using floor ', floor((@t1 / 100))as [23456/100], floor((-@t1 / 100))as [-23456/100]

Did I miss something?
Marc
Post #1349036
jennym
Posted Thursday, August 23, 2012 5:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:03 PM
Points: 6, Visits: 24
Interesting and good to know! I come from a functional background, and when I encountered this did not know it was a 'standard' way to do things. At the time I was much stronger in Crystal than straight TSQL, and did parse & concatonate as you mention. Ended up with the below to get user friendly times. Obviously this only worked because the value was a time, not a duration. Had it been a duration I would have been one of those you mention who put something together that "fell down" for anything over 24 hours. Thanks for an improved way to approach this!!!


select {tblScheduleDetail.StartTime}
Case 0:
"12:00 AM"

Case 1 to 999:
"12:0"&left(cstr({tblScheduleDetail.StartTime}),1)
&" AM"

Case 1000 to 9999:
"12:"&left(cstr({tblScheduleDetail.StartTime}),1)& mid(cstr({tblScheduleDetail.StartTime}),3,1)
&" AM"

Case 10000 to 99999:
left(cstr({tblScheduleDetail.StartTime}),1)&":"& mid(cstr({tblScheduleDetail.StartTime}),2,1)&
mid(cstr({tblScheduleDetail.StartTime}),4,1)&" AM"

case 100000 to 119999:
left(cstr({tblScheduleDetail.StartTime}),2)&":"& mid(cstr({tblScheduleDetail.StartTime}),3,1)&
mid(cstr({tblScheduleDetail.StartTime}),5,1)&" AM"

case 120000 to 240000:
left(cstr({tblScheduleDetail.StartTime}),2)&":"& mid(cstr({tblScheduleDetail.StartTime}),3,1)&
mid(cstr({tblScheduleDetail.StartTime}),5,1)&" PM"

;;
Post #1349047
rmechaber
Posted Thursday, August 23, 2012 6:18 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:43 AM
Points: 656, Visits: 2,921
I confess this bit me hard in the #$% a while ago, twice: once when I found out that date and time were stored as an INT representation, once again when code failed b/c I forgot there could be a zero value for time with only 1 digit.

I won't present the following code I developed as a great solution, but b/c it's admin. code that runs but once a day as part of maintenance, I don't really care about performance. It works, and that was sufficient:

	SELECT
		j.name AS JobName,	--	NVARCHAR(128)
		jh.step_id,			--	INT
		jh.step_name,		--	NVARCHAR(128)
		jh.sql_message_id,	--	INT
		jh.sql_severity,	--	INT
	--	sysjobhistory natively stores run_date and run_time as separate integers.  Combine and convert to DATETIME.  Why MS, why??
		CAST
		(
		--	Date portion, which will always be an 8-digit INT in the form yyyymmdd:
			CAST(jh.run_date AS VARCHAR(8)) + ' ' +
		--	Time portion is harder, b/c it can be 0, nnnnn (5 digits), or nnnnnn (6 digits) in the form hmmss.  No leading zero.
		--	This construct will prepend 6 zeroes, then take the rightmost 6 characters, yielding a 6-character string:
			--	RIGHT('000000' + CAST(run_time AS VARCHAR(6)), 6)
		--	We then slice and re-format to hh:mm:ss and combine with the date, then cast the whole shebang as DATETIME.
			LEFT(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 2) + ':' +
			Substring(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 3, 2) + ':' +
			RIGHT(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 2)
		AS DATETIME) As RunDateTime,
		jh.message,			--	NVARCHAR(1024)
		jh.run_status,		--	INT
		jh.run_duration		--	INT
	INTO #t
	FROM MSDB.dbo.sysjobs j INNER JOIN
		MSDB.dbo.sysjobhistory jh ON j.job_id = jh.job_id
	WHERE jh.sql_severity > 0 OR
		jh.run_status = 0

Rich
Post #1349056
Michael.Beeby
Posted Thursday, August 23, 2012 7:02 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 4:14 AM
Points: 198, Visits: 588
I did wonder about STUFF, e.g.
SELECT STUFF ('200', 2 , 0, ':')
DECLARE @MJBtime INT
SET @MJBtime = '10200'
SELECT LEN(@MJBtime)
SELECT STUFF (@MJBtime, LEN(@MJBtime)-1, 0, ':') --Not 2 as 1st value before first character
SELECT STUFF(STUFF (@MJBtime, LEN(@MJBtime)-1, 0, ':'),LEN(@MJBtime)-3,0,':')
Post #1349076
Kyle Neier ,
Posted Thursday, August 23, 2012 8:19 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:52 AM
Points: 194, Visits: 1,059

Marc ~

I don't believe you are missing anything.

That seems to be a perfectly viable alternative - one that I was unaware of until today.

Thanks for sharing!
Post #1349136
SQL-Tucker
Posted Thursday, August 23, 2012 10:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 23, 2012 10:05 AM
Points: 14, Visits: 31
Thanks for the article, I always enjoy exploring new ways to tackle persistent challenges.
I particularly like that it handles variable length run_duration values.
Is there a performance gain using the math approach vs the string manipulation?

I'm novice to PowerShell so I don't have experience benchmaking its performance. I'm curious what the performance would be compared to the conventional method converting the time at extraction using string functions.

How can the PowerShell option be applied as a function?
(forgive the novice, the only way I've called PowerShell in T-SQL was through xp_cmdshell)

And does it serve as a performant alternative to the string manipulation (see example)?
I'm aware the below example will fail when the duration exceeds 99 hours, but if a job is running for 99 hours in my environment there are bigger issues than being able to return the time in HH:MM:SS format. So handling a variable length run_duration is not a good use of resources for my environment.

SELECT 
	 stuff(stuff(left('000000',6-len(run_duration))+cast(run_duration AS VARCHAR),5,0,':'),3,0,':')
	,run_duration
	,*
FROM msdb.dbo.sysjobhistory WITH(NOLOCK)

result sample
(No column name)	run_duration	instance_id	job_id
00:00:02		2		12723		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:02		2		12724		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:02		2		12725		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:02		2		12726		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:02		2		12727		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:02		2		12728		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:02		2		12729		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:03		3		12730		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:10		10		12731		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:11		11		12732		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:25:34		2534		12733		95BD60FC-DF27-45A1-AAC4-7CA4EA90442E
00:25:35		2535		12734		95BD60FC-DF27-45A1-AAC4-7CA4EA90442E

Thoughts? Tips on benchmarking PS performance?

[update]
I had an oversight... the point in the article is to return the duration in seconds. However what I posted above only returns formatted time, for duration in seconds I should have posted this:
datediff(ss,0,cast(stuff(stuff(left('000000',6-len(run_duration))+cast(run_duration AS VARCHAR),5,0,':'),3,0,':') AS DATETIME))
Post #1349235
rmechaber
Posted Thursday, August 23, 2012 10:10 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:43 AM
Points: 656, Visits: 2,921
SQL-Tucker (8/23/2012)
Thanks for the article, I always enjoy exploring new ways to tackle persistent challenges.
I particularly like that it handles variable length run_duration values.
Is there a performance gain using the math approach vs the string manipulation?

I'm novice to PowerShell so I don't have experience benchmaking its performance. I'm curious what the performance would be compared to the conventional method converting the time at extraction using string functions.

How can the PowerShell option be applied as a function?
(forgive the novice, the only way I've called PowerShell in T-SQL was through xp_cmdshell)

And does it serve as a performant alternative to the string manipulation (see example)?
I'm aware the below example will fail when the duration exceeds 99 hours, but if a job is running for 99 hours in my environment there are bigger issues than being able to return the time in HH:MM:SS format. So handling a variable length run_duration is not a good use of resources for my environment.
SELECT 
	 stuff(stuff(left('000000',6-len(run_duration))+cast(run_duration AS VARCHAR),5,0,':'),3,0,':')
	,run_duration
	,*
FROM msdb.dbo.sysjobhistory WITH(NOLOCK)

result sample
(No column name)	run_duration	instance_id	job_id
00:00:02		2		12723		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:02		2		12724		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:02		2		12725		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:02		2		12726		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:02		2		12727		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:02		2		12728		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:02		2		12729		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:03		3		12730		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:10		10		12731		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:00:11		11		12732		D4C68B9E-C8BF-4B71-ADE6-062BA0014D78
00:25:34		2534		12733		95BD60FC-DF27-45A1-AAC4-7CA4EA90442E
00:25:35		2535		12734		95BD60FC-DF27-45A1-AAC4-7CA4EA90442E

Thoughts? Tips on benchmarking PS performance?


That code errors out on my trial:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

Rich
Post #1349239
Jason-
Posted Thursday, August 23, 2012 11:49 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:58 PM
Points: 522, Visits: 424
rmechaber (8/23/2012)
That code errors out on my trial:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

Rich


I've seen this before. There is a negative value in your duration ( like -954448987) While I don't know what causes this (maybe a SQL Agent bug) it is the reason for failing as the value is more than 6 characters. clear your agent history to get rid of the negative value or add a where clause "WHERE run_duration > 0"

EDIT: Could also be a duration greater than 99 hours. In which case the where clause would be "WHERE run_duration BETWEEN 0 and 999999"

-
Post #1349277
rmechaber
Posted Thursday, August 23, 2012 12:00 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:43 AM
Points: 656, Visits: 2,921
Jason- (8/23/2012)
rmechaber (8/23/2012)
That code errors out on my trial:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

Rich


I've seen this before. There is a negative value in your duration ( like -954448987) While I don't know what causes this (maybe a SQL Agent bug) it is the reason for failing as the value is more than 6 characters. clear your agent history to get rid of the negative value or add a where clause "WHERE run_duration > 0"

Good call, that was the issue. Why the Agent log has a negative run duration, I don't know; Googling only turned up more "me, too *shrugs*" posts, without an explanation.

If anyone here has an answer, that would be nice. The job in question was an Index rebuild subplan in a maintanance plan.

Rich
Post #1349280
« Prev Topic | Next Topic »

Add to briefcase
17 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.