检查SQL Server Job状态

[sql]代码库

方法1  
EXEC msdb.dbo.sp_help_job @execution_status = 1

方法2  
EXEC msdb.dbo.sp_help_job @execution_status = 1
 
SELECT DISTINCT j.NAME AS "Job Name"
    ,j.description AS "Job Description"
    ,h.run_date AS LastStatusDate
    ,h.run_time AS LastStatusTime
    ,h.run_duration AS LastStatusDuration
    ,CASE h.run_status
        WHEN 0
            THEN 'Failed'
        WHEN 1
            THEN 'Successful'
        WHEN 3
            THEN 'Cancelled'
        WHEN 4
            THEN 'In Progress'
        END AS JobStatus
FROM msdb..sysJobHistory h
    ,msdb..sysJobs j
WHERE j.job_id = h.job_id
    AND h.step_id = 1
    AND h.run_date = (
        SELECT max(hi.run_date)
        FROM msdb..sysJobHistory hi
        WHERE h.job_id = hi.job_id
        )
    AND h.run_time = (
        SELECT max(hj.run_time)
        FROM msdb..sysJobHistory hj
        WHERE h.job_id = hj.job_id
        )
ORDER BY 1

方法3  
SELECT DISTINCT cat.NAME AS "Category"
    ,j.NAME AS "Job Name"
    ,j.description AS "Job Description"
    ,h.run_date AS LastStatusDate
    ,h.run_time AS LastStatusTime
    ,h.run_duration AS LastStatusDuration
    ,CASE h.run_status
        WHEN 0
            THEN 'Failed'
        WHEN 1
            THEN 'Successful'
        WHEN 3
            THEN 'Cancelled'
        WHEN 4
            THEN 'In Progress'
        END AS JobStatus
FROM msdb..sysJobHistory h
    ,msdb..sysJobs j
    ,msdb..syscategories cat
WHERE j.job_id = h.job_id
    AND j.category_id = cat.category_id
    AND h.step_id = 1
    AND h.run_date = (
        SELECT max(hi.run_date)
        FROM msdb..sysJobHistory hi
        WHERE h.job_id = hi.job_id
        )
    AND h.run_time = (
        SELECT max(hj.run_time)
        FROM msdb..sysJobHistory hj
        WHERE h.job_id = hj.job_id
        )
ORDER BY 1
    ,3

方法4 
SELECT [Job Name] = j.NAME
    ,[Job Description] = j.description
    ,[LastRunDate] = h.run_date
    ,[LastRunTime] = h.run_time
    ,[JobStatus] = CASE h.run_status
        WHEN 0
            THEN 'Failed'
        WHEN 1
            THEN 'Successful'
        WHEN 3
            THEN 'Cancelled'
        WHEN 4
            THEN 'In Progress'
        END
    ,[OrderOfRun] = Rank() OVER (
        PARTITION BY j.job_id ORDER BY h.run_date DESC
            ,h.run_time DESC
        )
FROM msdb.dbo.sysjobhistory h
LEFT JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE h.step_id = 0 --only look @ Job Outcome step
ORDER BY [Job Name] DESC
    ,Rank() OVER (
        PARTITION BY j.job_id ORDER BY h.run_date DESC
            ,h.run_time DESC
        ) ASC