October 9, 2009 at 11:08 am
Gurus,
I have a task that I have no idea on how to approach. I need to look at a job on two separate dates and give the percentage of how much longer it took. I have searched online and books but cant find any information on how to do this.
Appreciate your help!!!
October 9, 2009 at 1:58 pm
One issue you will run into is that the sysjobhistory table is purged regularly and only keeps a certain # of total rows and certain # of rows per job.
Do you want per step or per job? Something like this would work for per job and you should be able to figure out how to get it by step from here:
;WITH cteJobHistory AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY run_date desc, run_time desc) AS row_Id,
S.run_date,
S.run_time,
S2.job_id,
S2.[name],
SUM(S.run_duration) AS run_duration
FROM
msdb.dbo.sysjobhistory AS S JOIN
msdb.dbo.sysjobs AS S2
ON S.job_id = S2.job_id
WHERE
S2.[name] = 'Backup -System DBs' AND
S.step_id = 0 -- this is the final outcome
GROUP BY
S.run_date,
S.run_time,
S2.job_id,
S2.[name]
)
SELECT
*,
A.run_duration - B.run_duration AS run_duration_difference
FROM
cteJobHistory AS A JOIN
cteJobHistory AS B ON
A.row_id = B.row_id - 1
WHERE
A.row_id = 1
Edit: Sorry this doesn't give percentage, but I think you can probably get to that from what I posted
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply