Ygthkb

Why do we interpret the accelerated expansion of the universe as the proof for the existence of dark energy?

Euler and minus sign

Taking an academic pseudonym?

Is it ethical to apply for a job on someone's behalf?

Was the Soviet N1 really capable of sending 9.6 GB/s of telemetry?

Spells that would be effective against a Modern Day army but would NOT destroy a fantasy one

How do I handle a blinded enemy which wants to attack someone it's sure is there?

Manager has noticed coworker's excessive breaks. Should I warn him?

Variance of sine and cosine of a random variable

Is it common to refer to someone as "Prof. Dr. [LastName]"?

Why is quixotic not Quixotic (a proper adjective)?

Isn't a semicolon (';') needed after a function declaration in C++?

What happens if both players misunderstand the game state until it's too late?

How to know if I am a 'Real Developer'

Build ASCII Podiums

Dot product with a constant

Why does this quiz question say that protons and electrons do not combine to form neutrons?

Have any astronauts or cosmonauts died in space?

Why is Shelob considered evil?

How can changes in personality/values of a person who turned into a vampire be explained?

Buying a "Used" Router

How does holding onto an active but un-used credit card affect your ability to get a loan?

Do these large-scale, human power-plant-tending robots from the Matrix movies have a name, in-universe or out?

Is there a way to pause a running process on Linux systems and resume later?

How do I get a run duration in minutes for each individual step in a single Job?

Vendor Wants to Run MSDB job every 5 minutes for Business ApplicationSQL Job step run as different sql accountHow SQL Server agent calculates next run date for a jobRun a specific SQL Server job step from a stored proceduresql agent job: How to set max run time for powershell stepWhat to do when Sql Server Agent refuses to run a Job Step that references a missing database snapshotWeekly SQL database backup failingGet Job step run for all jobs and job steps using TSQLWhat is considered for output from an sql server job step?Linking job steps from a single run together in sysjobhistory

1

The Run_Duration in sysjobhistory of the the complete job. Is there a way to get this? [msdb].[dbo].[sysjobactivity] only has the last step.

sql-server sql-server-2012 t-sql jobs

share|improve this question

edited May 24 '18 at 16:48

MDCCL

6,80331745

asked May 24 '18 at 15:33

MichaelMichael

114

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Look at last_run_duration in sysjobsteps
  
  – scsimon
  May 24 '18 at 15:58
  

  
  
  
  

add a comment | 

1

The Run_Duration in sysjobhistory of the the complete job. Is there a way to get this? [msdb].[dbo].[sysjobactivity] only has the last step.

sql-server sql-server-2012 t-sql jobs

share|improve this question

edited May 24 '18 at 16:48

MDCCL

6,80331745

asked May 24 '18 at 15:33

MichaelMichael

114

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Look at last_run_duration in sysjobsteps
  
  – scsimon
  May 24 '18 at 15:58
  

  
  
  
  

add a comment | 

The Run_Duration in sysjobhistory of the the complete job. Is there a way to get this? [msdb].[dbo].[sysjobactivity] only has the last step.

sql-server sql-server-2012 t-sql jobs

share|improve this question

edited May 24 '18 at 16:48

MDCCL

6,80331745

asked May 24 '18 at 15:33

MichaelMichael

114

share|improve this question

edited May 24 '18 at 16:48

MDCCL

6,80331745

asked May 24 '18 at 15:33

MichaelMichael

114

share|improve this question

edited May 24 '18 at 16:48

MDCCL

6,80331745

asked May 24 '18 at 15:33

MichaelMichael

114

edited May 24 '18 at 16:48

MDCCL

6,80331745

edited May 24 '18 at 16:48

MDCCL

6,80331745

asked May 24 '18 at 15:33

MichaelMichael

114

asked May 24 '18 at 15:33

MichaelMichael

114

2 Answers
2

active

oldest

votes

2

This should get you started...

select 

    jobs.name

    ,jobs.description

    ,steps.step_id

    ,steps.step_name

    ,steps.last_run_outcome

    ,last_run_time = stuff(stuff(right('00000' + cast(steps.last_run_time as varchar),6),3,0,':'),6,0,':')

    ,last_run_duration = stuff(stuff(right('00000' + cast(steps.last_run_duration as varchar),6),3,0,':'),6,0,':')

from [msdb].[dbo].[sysjobs] jobs

inner join [msdb].[dbo].[sysjobsteps] steps on

steps.job_id = jobs.job_id

order by jobs.job_id, steps.step_id

share|improve this answer

answered May 24 '18 at 16:05

scsimonscsimon

1,398414

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In this case you use [sysjobsteps] where I have my ALL data stored in [sysjobHistory]
  
  – Michael
  May 25 '18 at 8:11
  

  
  
  
  

add a comment | 

0

In this case you use [sysjobsteps] where I have my ALL data stored in [sysjobHistory] This below gives duration in seconds though. My suggestion:

SELECT top 1000 j.name as JobName, step_id, jh.step_name StepName,
CONVERT(CHAR(10), CAST(STR(jh.run_date,8, 0) AS dateTIME), 112) as RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( jh.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
STUFF(STUFF(RIGHT('000000' + CAST ( jh.run_duration AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunDuration,
case jh.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,
FROM sysjobhistory jh inner join sysjobs j
ON j.job_id = jh.job_id
ORDER BY j.name, jh.run_date, jh.run_time

share|improve this answer

edited 3 mins ago

Yaroslav

2,04711334

answered May 25 '18 at 8:30

MichaelMichael

114

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f207748%2fhow-do-i-get-a-run-duration-in-minutes-for-each-individual-step-in-a-single-job%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2

This should get you started...

select 

    jobs.name

    ,jobs.description

    ,steps.step_id

    ,steps.step_name

    ,steps.last_run_outcome

    ,last_run_time = stuff(stuff(right('00000' + cast(steps.last_run_time as varchar),6),3,0,':'),6,0,':')

    ,last_run_duration = stuff(stuff(right('00000' + cast(steps.last_run_duration as varchar),6),3,0,':'),6,0,':')

from [msdb].[dbo].[sysjobs] jobs

inner join [msdb].[dbo].[sysjobsteps] steps on

steps.job_id = jobs.job_id

order by jobs.job_id, steps.step_id

share|improve this answer

answered May 24 '18 at 16:05

scsimonscsimon

1,398414

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In this case you use [sysjobsteps] where I have my ALL data stored in [sysjobHistory]
  
  – Michael
  May 25 '18 at 8:11
  

  
  
  
  

add a comment | 

2

This should get you started...

select 

    jobs.name

    ,jobs.description

    ,steps.step_id

    ,steps.step_name

    ,steps.last_run_outcome

    ,last_run_time = stuff(stuff(right('00000' + cast(steps.last_run_time as varchar),6),3,0,':'),6,0,':')

    ,last_run_duration = stuff(stuff(right('00000' + cast(steps.last_run_duration as varchar),6),3,0,':'),6,0,':')

from [msdb].[dbo].[sysjobs] jobs

inner join [msdb].[dbo].[sysjobsteps] steps on

steps.job_id = jobs.job_id

order by jobs.job_id, steps.step_id

share|improve this answer

answered May 24 '18 at 16:05

scsimonscsimon

1,398414

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In this case you use [sysjobsteps] where I have my ALL data stored in [sysjobHistory]
  
  – Michael
  May 25 '18 at 8:11
  

  
  
  
  

add a comment | 

This should get you started...

select 

    jobs.name

    ,jobs.description

    ,steps.step_id

    ,steps.step_name

    ,steps.last_run_outcome

    ,last_run_time = stuff(stuff(right('00000' + cast(steps.last_run_time as varchar),6),3,0,':'),6,0,':')

    ,last_run_duration = stuff(stuff(right('00000' + cast(steps.last_run_duration as varchar),6),3,0,':'),6,0,':')

from [msdb].[dbo].[sysjobs] jobs

inner join [msdb].[dbo].[sysjobsteps] steps on

steps.job_id = jobs.job_id

order by jobs.job_id, steps.step_id

share|improve this answer

answered May 24 '18 at 16:05

scsimonscsimon

1,398414

select 

    jobs.name

    ,jobs.description

    ,steps.step_id

    ,steps.step_name

    ,steps.last_run_outcome

    ,last_run_time = stuff(stuff(right('00000' + cast(steps.last_run_time as varchar),6),3,0,':'),6,0,':')

    ,last_run_duration = stuff(stuff(right('00000' + cast(steps.last_run_duration as varchar),6),3,0,':'),6,0,':')

from [msdb].[dbo].[sysjobs] jobs

inner join [msdb].[dbo].[sysjobsteps] steps on

steps.job_id = jobs.job_id

order by jobs.job_id, steps.step_id

share|improve this answer

answered May 24 '18 at 16:05

scsimonscsimon

1,398414

answered May 24 '18 at 16:05

scsimonscsimon

1,398414

answered May 24 '18 at 16:05

scsimonscsimon

1,398414

0

In this case you use [sysjobsteps] where I have my ALL data stored in [sysjobHistory] This below gives duration in seconds though. My suggestion:

SELECT top 1000 j.name as JobName, step_id, jh.step_name StepName,
CONVERT(CHAR(10), CAST(STR(jh.run_date,8, 0) AS dateTIME), 112) as RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( jh.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
STUFF(STUFF(RIGHT('000000' + CAST ( jh.run_duration AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunDuration,
case jh.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,
FROM sysjobhistory jh inner join sysjobs j
ON j.job_id = jh.job_id
ORDER BY j.name, jh.run_date, jh.run_time

share|improve this answer

edited 3 mins ago

Yaroslav

2,04711334

answered May 25 '18 at 8:30

MichaelMichael

114

add a comment | 

0

In this case you use [sysjobsteps] where I have my ALL data stored in [sysjobHistory] This below gives duration in seconds though. My suggestion:

SELECT top 1000 j.name as JobName, step_id, jh.step_name StepName,
CONVERT(CHAR(10), CAST(STR(jh.run_date,8, 0) AS dateTIME), 112) as RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( jh.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
STUFF(STUFF(RIGHT('000000' + CAST ( jh.run_duration AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunDuration,
case jh.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,
FROM sysjobhistory jh inner join sysjobs j
ON j.job_id = jh.job_id
ORDER BY j.name, jh.run_date, jh.run_time

share|improve this answer

edited 3 mins ago

Yaroslav

2,04711334

answered May 25 '18 at 8:30

MichaelMichael

114

add a comment | 

In this case you use [sysjobsteps] where I have my ALL data stored in [sysjobHistory] This below gives duration in seconds though. My suggestion:

SELECT top 1000 j.name as JobName, step_id, jh.step_name StepName,
CONVERT(CHAR(10), CAST(STR(jh.run_date,8, 0) AS dateTIME), 112) as RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( jh.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
STUFF(STUFF(RIGHT('000000' + CAST ( jh.run_duration AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunDuration,
case jh.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,
FROM sysjobhistory jh inner join sysjobs j
ON j.job_id = jh.job_id
ORDER BY j.name, jh.run_date, jh.run_time

share|improve this answer

edited 3 mins ago

Yaroslav

2,04711334

answered May 25 '18 at 8:30

MichaelMichael

114

share|improve this answer

edited 3 mins ago

Yaroslav

2,04711334

answered May 25 '18 at 8:30

MichaelMichael

114

edited 3 mins ago

Yaroslav

2,04711334

edited 3 mins ago

Yaroslav

2,04711334

answered May 25 '18 at 8:30

MichaelMichael

114

answered May 25 '18 at 8:30

MichaelMichael

114