December 22, 2005 at 7:24 am
Hi
I have a job for which I have just viewed the job history, most are showing successful with a run duration of around 2.30 minutes or unsuccessful with a duration of 0.01. however, there is one successful entry with a duration of -95443:-91:-84
Has anybody seen anything like this and is it anything to be concerned about ?
Cheers
December 22, 2005 at 10:04 am
To make data readable,this is how the conversion of data takes place
from sysjobs and sysjobhistory
SELECT sj.originating_server, sj.name,
--What is it in English
CASE sjh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
ELSE 'Unknown'
END,
--Convert Integer date to regular datetime
SUBSTRING(CAST(sjh.run_date AS CHAR(8)),5,2) + '/' +
RIGHT(CAST(sjh.run_date AS CHAR(8)),2) + '/' +
LEFT(CAST(sjh.run_date AS CHAR(8)),4)
--Change run time into something you can recognize (hh:mm:ss)
, LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2)
--Change run duration into something you can recognize (hh:mm:ss)
, LEFT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2)
FROM dev2.msdb.dbo.sysjobs sj --job id and name
--Job history
INNER JOIN dev2.msdb.dbo.sysjobhistory sjh
ON sj.job_id = sjh.job_id
--Join for new history rows
left JOIN msdb.dbo.tJobReport jr
ON sj.originating_server = jr.server
AND sj.name = jr.jobname
AND SUBSTRING(CAST(sjh.run_date AS CHAR(8)),5,2) + '/' +
RIGHT(CAST(sjh.run_date AS CHAR(8)),2) + '/' +
LEFT(CAST(sjh.run_date AS CHAR(8)),4) = jr.rundate
AND LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) = jr.runtime
--Only enabled jobs
WHERE sj.enabled = 1
--Only job outcome not each step outcome
AND sjh.step_id = 0
--Only completed jobs
AND sjh.run_status <> 4
--Only new data
AND jr.lngID IS NULL
--Latest date first
ORDER BY sjh.run_date DESC
GO
This query is from good explanation given by Randy Dyess in sql-server-performance.com.
In your case it looks like some issuers in this vconversion when data is converted in E.M.
Hope this helps,
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply