SQL Agent Job Logs - Can't get the Error Log - Andy M Mallon

Question

I’ve some logs that I want to extract to a report in order to get the SQL_Agent_Jobs status that runs during the night. If the Job fails then I want to receive the Error Log in a table. I am trying to get that information with this query:

DECLARE @job_id UNIQUEIDENTIFIER

SELECT 
    @job_id = job_id FROM msdb.dbo.sysjobs 
WHERE 
       [name] like '%JOB_1%'
    OR [name] like '%JOB_2%'
    OR [name] like '%JOB_3%'
    OR [name] like '%JOB_4%'
    OR [name] like '%JOB_5%'

;WITH Error_Output (job_id, error_log) AS
(
    SELECT 
        JS.job_id,
        CASE
        WHEN JSL.[log] IS NULL THEN JH.[Message]
        ELSE JSL.[log]
        END AS LogOutput
    FROM 
        msdb.dbo.sysjobsteps JS 
        INNER JOIN msdb.dbo.sysjobhistory JH 
            ON JS.job_id = JH.job_id AND JS.step_id = JH.step_id 
        LEFT OUTER JOIN msdb.dbo.sysjobstepslogs JSL
            ON JS.step_uid = JSL.step_uid
    WHERE 
        INSTANCE_ID &gt;
        (SELECT 
            MIN(INSTANCE_ID)
        FROM 
            (SELECT top (2) 
                INSTANCE_ID, job_id
            FROM 
                msdb.dbo.sysjobhistory
            WHERE 
                job_id = @job_id
                AND STEP_ID = 0
            ORDER BY 
                INSTANCE_ID desc
            ) A
        )
        AND JS.step_id &lt;&gt; 0 
        AND JH.job_id = @job_id
        AND JH.run_status = 0
)
SELECT 
    sj.name AS [Job Name]
    ,MAX(sja.run_requested_date) AS [Start Date]
    ,MAX(sja.start_execution_date) AS [Effective Start Date]
    ,MAX(sja.stop_execution_date) AS [Effective End Date]
    ,DATEDIFF(MINUTE, MAX(sja.start_execution_date), MAX(sja.stop_execution_date)) as [Job Execution Time -  M]
    ,CASE WHEN error_log IS NULL THEN 'Job runs successfully' else error_log end as [Job Log]
FROM 
    msdb.dbo.sysjobactivity AS sja
    INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
    LEFT OUTER JOIN Error_Output AS error ON error.job_id = sja.job_id AND error.job_id = sj.job_id
WHERE 
       [name] like '%JOB_1%'
    OR [name] like '%JOB_2%'
    OR [name] like '%JOB_3%'
    OR [name] like '%JOB_4%'
    OR [name] like '%JOB_5%'
GROUP BY 
    sj.name, error_log

However, I’m always getting that the Job runs ‘Job runs successfully’ and I already test and see that the job failed in execution. I even configured SQL_Agent_Job to receive email whenever the job failed, and I have received the emails and the status in the query does not change to the error log (failed).

What I’m doing wrong?

asked 2017-06-25 by John_Rodgers

Answer

I have written a post about retrieving job status from msdb, which you can see here. The code in that post does not pull in data from dbo.sysjobstepslogs, but it’s pretty straightforward to extend that code to pull in the last error message from dbo.sysjobstepslogs if you are logging to that table.

By default, job steps do not log to msdb. To configure your job steps to log to dbo.sysjobstepslogs, you must go into the job step properties for every step in the job, visit the “Advanced” tab, and select the option for “Log to table”:

You can then create this Table-Valued Function to get the most recent job status. I’ve modified the code from the post above to also pull the most recent error from the job, if it is available:

 CREATE FUNCTION dbo.SqlAgentJob_GetStatus (@JobName sysname)
     RETURNS TABLE
 AS
 RETURN
 SELECT TOP 1
     JobName        = j.name,
     IsRunning      = CASE
                        WHEN ja.job_id IS NOT NULL
                            AND ja.stop_execution_date IS NULL
                          THEN 1 ELSE 0 
                        END,
     RequestSource  = ja.run_requested_source,
     LastRunTime    = ja.start_execution_date,
     NextRunTime    = ja.next_scheduled_run_date,
     LastJobStep    = js.step_name,
     RetryAttempt   = jh.retries_attempted,
     JobLastOutcome = CASE
                        WHEN ja.job_id IS NOT NULL
                            AND ja.stop_execution_date IS NULL THEN 'Running'
                        WHEN jh.run_status = 0 THEN 'Failed'
                        WHEN jh.run_status = 1 THEN 'Succeeded'
                        WHEN jh.run_status = 2 THEN 'Retry'
                        WHEN jh.run_status = 3 THEN 'Cancelled'
                      END,
     LastError = (SELECT TOP 1 jsl.log 
                 FROM msdb.dbo.sysjobstepslogs jsl 
                 WHERE jsl.step_uid = js.step_uid
                 AND jsl.date_modified &gt;= ja.start_execution_date)
 FROM msdb.dbo.sysjobs j
 LEFT JOIN msdb.dbo.sysjobactivity ja 
     ON ja.job_id = j.job_id
        AND ja.run_requested_date IS NOT NULL
        AND ja.start_execution_date IS NOT NULL
 LEFT JOIN msdb.dbo.sysjobsteps js
     ON js.job_id = ja.job_id
        AND js.step_id = ja.last_executed_step_id
 LEFT JOIN msdb.dbo.sysjobhistory jh
     ON jh.job_id = j.job_id
        AND jh.instance_id = ja.job_history_id
 WHERE j.name = @JobName
 ORDER BY ja.start_execution_date DESC;
 GO

Finally, you can leverage the TVF to query for your job status, last step to run, last logged error, etc:

 --Specific jobs
 SELECT sts.*
 FROM msdb.dbo.sysjobs j
 CROSS APPLY dbo.SqlAgentJob_GetStatus (j.name) sts
 WHERE 
        j.[name] like '%JOB_1%'
     OR j.[name] like '%JOB_2%'
     OR j.[name] like '%JOB_3%'
     OR j.[name] like '%JOB_4%'
     OR j.[name] like '%JOB_5%';
 --Failed Jobs
 SELECT sts.*
 FROM msdb.dbo.sysjobs j
 CROSS APPLY dbo.SqlAgentJob_GetStatus (j.name) sts
 WHERE sts.JobLastOutcome &lt;&gt; 'Succeeded';

answered 2017-06-25 by Andy Mallon