用户注册



邮箱:

密码:

用户登录


邮箱:

密码:
记住登录一个月忘记密码?

发表随想


还能输入:200字
云代码 - sql代码库

检查SQL Server Job状态

2016-08-14 作者: 小章举报

[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


网友评论    (发表评论)


发表评论:

评论须知:

  • 1、评论每次加2分,每天上限为30;
  • 2、请文明用语,共同创建干净的技术交流环境;
  • 3、若被发现提交非法信息,评论将会被删除,并且给予扣分处理,严重者给予封号处理;
  • 4、请勿发布广告信息或其他无关评论,否则将会删除评论并扣分,严重者给予封号处理。


扫码下载

加载中,请稍后...

输入口令后可复制整站源码

加载中,请稍后...