SQL Server Job查看

--查看job 
SELECT job_id, [name] FROM msdb.dbo.sysjobs

--查看job step
SELECT job.job_id,notify_level_email ,name,
enabled,description ,step_name,command,SERVER,database_name
FROM msdb.dbo.sysjobs job JOIN msdb.dbo.sysjobsteps steps
ON job.job_id = steps.job_id

--查看job history 歷程1, 最多1000筆(可在agent設size)
SELECT j.job_id, j.name, h.run_duration,h.step_id    FROM sysjobs j
INNER JOIN sysjobhistory h ON h.job_id=j.job_id
INNER JOIN (
SELECT job_id, MAX(STR(run_date,8)+STR(run_time,8)) AS LastRunDate
FROM sysjobhistory GROUP BY job_id
) x ON j.job_id=x.job_id
WHERE h.step_id=0 
AND STR(h.run_date,8)+STR(h.run_time,8)=x.LastRunDate

--查看job history 歷程2
SELECT run_date, run_time FROM sysjobhistory a INNER JOIN sysjobs b
ON a.job_id = b.job_id
WHERE  name LIKE  'OLAP%'

--查看job sysjobschedules
SELECT b.name,active_start_date,active_start_time,active_end_date,active_end_time
 FROM sysjobschedules a INNER JOIN sysjobs b
ON a.job_id = b.job_id
WHERE b.name LIKE 'OLAP%'