CODEWITHSUNDEEP
sql-servert-sqlsql-server-2016
brandont
brandont

Reputation: 59

SQL Server and sysjobhistory run_status query

I'm having trouble getting a query written to do what I want. In SQL Server there is a system table called msdb.dbo.sysjobhistory. It contains the history of the SQL agent jobs that run and perform operations on the server. Each row of the table corresponds to a step in the job. There is a column in the table called run_status, that gets an integer value to indicate the outcome of the job when it completed and got written into the table.

I track the information in this table with some reports and simple queries. I discovered an interesting case last week that I was not accounting for in my reports. In the table, even though each row is a step in the job, there is also a step 0 entry for the job that serves as an overall outcome. Normally I just look at the step 0 rows and get the run_status in that row for the job and use that as the outcome. However it is possible for a step in the job to have a 'failure' status, but the overall job outcome in step 0 has a 'succeeded' status. I came up with the following query to look for these situations:

SELECT job_id, step_id, step_name, run_date, run_time, run_status, W.parent_status
FROM msdb.dbo.sysjobhistory [H]
CROSS APPLY 
( SELECT TOP 1 run_status AS parent_status
  FROM msdb.dbo.sysjobhistory
  WHERE job_id = H.job_id
  AND run_date <= H.run_date
  AND step_id = 0
  AND (CASE WHEN run_date = H.run_date THEN run_Time ELSE H.run_time END) <= H.run_time
  ORDER BY run_date DESC, run_time DESC
) AS [W]
ORDER BY run_date, run_time, step_id

This query will give a result set similar to the following for one particular job_id:

job_id | step_id | step_name | run_date | run_time | run_status | parent_status
-------------------------------------------------------------------------------
A12345 |    0    | (outcome) | 20170112 |  40000   | 1          | 1
A12345 |    1    |   step 1  | 20170112 |  43000   | 1          | 1
A12345 |    2    |   step 2  | 20170112 |  50000   | 0          | 1
A12345 |    3    |   step 3  | 20170112 |  53000   | 1          | 1

In this situation, a run_status of 0 means failure, and a run_status of 1 means success. Now that I have this table, I would like to use it to find out what really happened with a job. I need to make a CASE statement or something similar that will "override" the run_status of step 0 when necessary. So if the situation above occurs, then say I want to make the run_status a value of 2 instead. So now when I query just the job outcome steps I'd get something like this for the above job:

job_id | step_id | step_name | run_date | run_time | run_status 
---------------------------------------------------------------
A12345 |    0    | (outcome) | 20170112 | 40000    |     2

I know what I want to do, I am just not sure how to write the SQL to do it. I need to somehow compare the parent status to all of the run_status results, and when parent status = 1 and run_status = 0, then change the run_status of step 0. Thanks for any assistance.

Upvotes: 0

Views: 3604

Answers (1)

Dean Savović
Dean Savović

Reputation: 739

select
    (case when sh.run_status = 1 and tt.job_id is not null then 2 else sh.run_status end) as run_status
    , *
from dbo.sysjobhistory sh
    outer apply (
        select
            top (1)
            tsh.job_id
        from dbo.sysjobhistory tsh
        where sh.job_id = tsh.job_id
            and sh.step_id <> tsh.step_id
            and tsh.run_status = 0
    ) tt

Upvotes: 1

Related Questions