April 3, 2008 at 8:08 am
HI - we have a job 'Job1' which runs every day at 3:00PM. Sometimes it gets failed and will restart. Now, I want to know the startdate,enddate, total duration for each run of the job for the last one month. I need a query to retrieve these details from msdb in the below format
Startdatetime Enddatetime Duration
thanks
erajendar
April 3, 2008 at 8:12 am
Try this 2 queries this will give you the details you are looking for
SELECT sj.name AS JobName,Count(*) AS COUNT,
AVG(( run_duration % 100 )+ -- seconds
(((run_duration/100) % 100 ) * 60) + -- minutes in seconds
(((run_duration/10000) % 100 ) * 3600)) AS AvgRunSec,
Max(( run_duration % 100 )+ -- seconds
(((run_duration/100) % 100 ) * 60) + -- minutes in seconds
(((run_duration/10000) % 100 ) * 3600)) AS MaxRunSec,
Min(( run_duration % 100 )+ -- seconds
(((run_duration/100) % 100 ) * 60) + -- minutes in seconds
(((run_duration/10000) % 100 ) * 3600)) AS MinRunSec
FROM MSDB..SYSJOBHISTORY sjh INNER Join MSDB..SYSJOBS sj ON sjh.job_id = sj.job_id
WHERE Run_Date >= CONVERT(VARCHAR(20),GETDATE()-1,112)
AND STEP_ID = 0
GROUP BY sj.name
-----------------------------------
Select sj.name as JobName ,step_id as StepId,step_name as StepName,
CASE when run_status = 1 then 'Success'
else 'Failed'
End as Status,
CAST ( LEFT(CAST(run_date as varchar(8)),4) + '/' + SUBSTRING(CAST(run_date as varchar(8)), 5,2) + '/' +
RIGHT(cast(run_date as varchar(8)), 2) + ' ' +
CAST( ((run_time/10000) %100) as varchar ) + ':' + CAST( ((run_time/100) %100) as varchar ) + ':' +
CAST( (run_time %100) as varchar )as datetime ) as RunDate,
( run_duration % 100 )+ -- seconds
(((run_duration/100) % 100 ) * 60) + -- minutes in seconds
(((run_duration/10000) % 100 ) * 3600)as RunSeconds
From Msdb..Sysjobhistory sjh inner join Msdb..Sysjobs sj on sjh.job_id = sj.job_id
Where Run_Date > = CONVERT(varchar(20),GETDATE()-1,112)
Order By RunDate
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
April 4, 2008 at 2:59 pm
Note that the Agent will only retain so much history (based on your settings) and so you may not have 30 days of history to query.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 19, 2008 at 11:46 pm
I have a query again around this issue.
The below query gives me the required results.
SELECT sj.name AS JobName,Count(*) AS COUNT,
AVG(( run_duration % 100 )+ -- seconds
(((run_duration/100) % 100 ) * 60) + -- minutes in seconds
(((run_duration/10000) % 100 ) * 3600)) AS AvgRunSec,
Max(( run_duration % 100 )+ -- seconds
(((run_duration/100) % 100 ) * 60) + -- minutes in seconds
(((run_duration/10000) % 100 ) * 3600)) AS MaxRunSec,
Min(( run_duration % 100 )+ -- seconds
(((run_duration/100) % 100 ) * 60) + -- minutes in seconds
(((run_duration/10000) % 100 ) * 3600)) AS MinRunSec
FROM MSDB..SYSJOBHISTORY sjh INNER Join MSDB..SYSJOBS sj ON sjh.job_id = sj.job_id
WHERE Run_Date >= CONVERT(VARCHAR(20),GETDATE()-1,112)
AND STEP_ID = 0
GROUP BY sj.name
However, I want to know the status of current running job, but, step_id=0 entry is available in Sysjobhistory only after job fails/succeeds. Is there any way to extract the above results for the current running job,
erajendar
May 20, 2008 at 5:41 am
SYSJOBHISTORY is only written to on completions: step completion and Job completion. To get current status you will have to start in SYSJOBACTIVITY.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply