??SQL Server???????????????

???????????? ???????[ 2013/2/4 14:02:20 ] ????????

???????????????????????????????????????????????????δ???????????2005????????2000????е???????????????????????????Ч????

??????????????????????У?run_duration?????г?????????????????int????????run_time???????????

SELECT j.name AS 'JobName' ??
         run_date ??
         run_time ??
         msdb.dbo.agent_datetime(run_date?? run_time) AS 'RunDateTime' ??
         run_duration
FROM    msdb.dbo.sysjobs j
         INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE   j.enabled = 1 --Only Enabled Jobs
ORDER BY JobName ??
         RunDateTime DESC

??????????￡?

??????????λ?????????????3λ?????????????????????????????????????????????????????????????????????洢????????????У?????????????д?????????????????????????

SELECT j.name AS 'JobName' ??
         run_date ??
         run_time ??
         msdb.dbo.agent_datetime(run_date?? run_time) AS 'RunDateTime' ??
         run_duration ??
         ( ( run_duration / 10000 * 3600 + ( run_duration / 100 ) % 100 * 60
             + run_duration % 100 + 31 ) / 60 ) AS 'RunDurationMinutes'
FROM    msdb.dbo.sysjobs j
         INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE   j.enabled = 1 --Only Enabled Jobs
ORDER BY JobName ??
         RunDateTime DESC

???????????????????????????????????????????

??????????ETL?????????????к???裬??????????Щ?????????????????????????????msdb.dbo.sysjobsteps??

SELECT j.name AS 'JobName' ??
         s.step_id AS 'Step' ??
         s.step_name AS 'StepName' ??
         msdb.dbo.agent_datetime(run_date?? run_time) AS 'RunDateTime' ??
         ( ( run_duration / 10000 * 3600 + ( run_duration / 100 ) % 100 * 60
             + run_duration % 100 + 31 ) / 60 ) AS 'RunDurationMinutes'
FROM    msdb.dbo.sysjobs j
         INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
         INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id
                                                AND s.step_id = h.step_id
                                                AND h.step_id <> 0
WHERE   j.enabled = 1   --Only Enabled Jobs
ORDER BY JobName ??
         RunDateTime DESC