Average runtime beyond 24 hr

Question

Average runtime beyond 24 hr

zaheer.timol

SSC Enthusiast

Points: 155
More actions
March 15, 2023 at 3:43 pm

#4161056

Hi i am trying to get the average full backup job in cms of every job that took over 12 hrs for a month.
The problem is that when it goes beyond 24 hr ,the query does not bring a valid results .
the query below is the closest i got to the valid result but having issue average this.

your help is much appreciated
use msdb
SELECT j.name JobName
--, h.step_name StepName
--, CAST(STR(h.run_date, 8, 0) AS DATETIME)
-- + CAST(STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)), 6),
-- 5, 0, ':'), 3, 0, ':') AS DATETIME) AS StartDatetime
, DATEADD(SECOND,
( ( h.run_duration / 1000000 ) * 86400 )
+ ( ( ( h.run_duration - ( ( h.run_duration / 1000000 )
* 1000000 ) ) / 10000 ) * 3600 )
+ ( ( ( h.run_duration - ( ( h.run_duration / 10000 ) * 10000 ) )
/ 100 ) * 60 ) + ( h.run_duration - ( h.run_duration
/ 100 ) * 100 ),
CAST(STR(h.run_date, 8, 0) AS DATETIME)
+ CAST(STUFF(STUFF(RIGHT('000000'
+ CAST (h.run_time AS VARCHAR(6)), 6),
5, 0, ':'), 3, 0, ':') AS DATETIME)) AS EndDatetime
, STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6,
0, ':') AS run_duration_formatted
, ( ( h.run_duration / 1000000 ) * 86400 ) + ( ( ( h.run_duration
- ( ( h.run_duration
/ 1000000 )
* 1000000 ) )
/ 10000 ) * 3600 )
+ ( ( ( h.run_duration - ( ( h.run_duration / 10000 ) * 10000 ) )
/ 100 ) * 60 ) + ( h.run_duration - ( h.run_duration / 100 )
* 100 ) AS RunDurationInSeconds
--, CASE h.run_status
-- WHEN 0 THEN 'failed'
-- WHEN 1 THEN 'Succeded'
-- WHEN 2 THEN 'Retry'
-- WHEN 3 THEN 'Cancelled'
-- WHEN 4 THEN 'In Progress'
-- END AS ExecutionStatus
--, h.message MessageGenerated
FROM sysjobhistory h
INNER JOIN sysjobs j
ON j.job_id = h.job_id
where name = 'DB_Maint - USER_DATABASES - FULL'
-- and run_date = 20230306
and run_date between '202302014' and '20230314'
---and RunDurationInSeconds > 43200
and step_id = 0
and run_status = 1
ORDER BY h.run_date DESC
, h.run_time desc

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101147 More actions · Answer 1

To get durations beyond 12 hours, I think you can just do:

WHERE h.run_duration > 120000

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 2

Without having to look much at the code you posted, what is the unit value of the h.run_time column?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)