How to find SQL agent job status in SQL Server using query



 To find sql server job status using sql query can be very useful for automation, I have used below to kick off another sql agent job with respect to status of current job.



;WITH CTE_SQLAGENT_JOB_STATUS
AS (
    SELECT DISTINCT NAME AS [JobName]
        ,s.step_id      ,s.step_name
        ,CASE
            WHEN [Enabled] = 1   THEN 'Enabled' ELSE 'Disabled'
            END [JobStatus]
        ,CASE
            WHEN SJH.run_status = 0 THEN 'Failed'
            WHEN SJH.run_status = 1 THEN 'Succeeded'
            WHEN SJH.run_status = 2 THEN 'Retry'
            WHEN SJH.run_status = 3 THEN 'Cancelled'
            WHEN SJH.run_status = 4 THEN 'In Progress'
            ELSE 'Unknown'
            END [JobOutcome]
        ,CONVERT(VARCHAR(8), sjh.run_date) [RunDate]
        ,CONVERT(VARCHAR(8), STUFF(STUFF(CONVERT(TIMESTAMP, RIGHT('000000' + CONVERT(VARCHAR(6), sjh.run_time), 6)), 3, 0, ':'), 6, 0, ':')) RunTime
        ,RANK() OVER (  PARTITION BY s.step_name ORDER BY sjh.run_date DESC
                ,sjh.run_time DESC   ) AS rn
        ,SJH.run_status
    FROM msdb..SYSJobs sj
    INNER JOIN msdb..SYSJobHistory sjh ON sj.job_id = sjh.job_id
    INNER JOIN msdb.dbo.sysjobsteps s ON sjh.job_id = s.job_id
    AND sjh.step_id = s.step_id
    WHERE (sj.NAME LIKE 'JOB NAME')
    AND sjh.run_date = CONVERT(CHAR, getdate(), 112)
    )

SELECT  [JobName]
FROM CTE_SQLAGENT_JOB_STATUS
WHERE rn = 1
AND run_status NOT IN (1,4)
and [JobName] = 'Job Name



No comments:

Post a Comment