How do I get a run duration in minutes for each individual step in a single Job?Vendor Wants to Run MSDB job...
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
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
edited May 24 '18 at 16:48
MDCCL
6,80331745
asked May 24 '18 at 15:33
MichaelMichael
114
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
edited May 24 '18 at 16:48
MDCCL
6,80331745
asked May 24 '18 at 15:33
MichaelMichael
114
Look atlast_run_durationinsysjobsteps
– 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
edited May 24 '18 at 16:48
MDCCL
6,80331745
asked May 24 '18 at 15:33
MichaelMichael
114
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
sql-server sql-server-2012 t-sql jobs
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
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
edited May 24 '18 at 16:48
MDCCL
6,80331745
6,80331745
asked May 24 '18 at 15:33
MichaelMichael
114
asked May 24 '18 at 15:33
MichaelMichael
114
asked May 24 '18 at 15:33
MichaelMichael
114
114
Look atlast_run_durationinsysjobsteps
– scsimon
May 24 '18 at 15:58
add a comment |
Look atlast_run_durationinsysjobsteps
– scsimon
May 24 '18 at 15:58
Look at
last_run_duration in sysjobsteps– scsimon
May 24 '18 at 15:58
Look at
last_run_duration in sysjobsteps– scsimon
May 24 '18 at 15:58
add a comment |
2 Answers
2
active
oldest
votes
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
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 |
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
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
});
}
});
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
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
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
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
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
answered May 24 '18 at 16:05
scsimonscsimon
1,398414
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
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
answered May 24 '18 at 16:05
scsimonscsimon
1,398414
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 |
In this case you use [sysjobsteps] where I have my ALL data stored in [sysjobHistory]
– Michael
May 25 '18 at 8:11
In this case you use [sysjobsteps] where I have my ALL data stored in [sysjobHistory]
– Michael
May 25 '18 at 8:11
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 |
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
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
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
edited 3 mins ago
Yaroslav
2,04711334
answered May 25 '18 at 8:30
MichaelMichael
114
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
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
edited 3 mins ago
Yaroslav
2,04711334
2,04711334
answered May 25 '18 at 8:30
MichaelMichael
114
answered May 25 '18 at 8:30
MichaelMichael
114
answered May 25 '18 at 8:30
MichaelMichael
114
114
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Look at
last_run_durationinsysjobsteps– scsimon
May 24 '18 at 15:58