0

I have a sql agent job step that failed for a given date / time. Is there an MSDB - TSQL query I can run to view the step failure reason given that :

  1. I know the job name.
  2. I know when the job failed.
  3. I know the step number
user2368632
  • 890
  • 4
  • 10

2 Answers2

1

You should be able to adjust this query to your criteria - taken from What is the Query to display the failed sql jobs

select j.name
    ,js.step_name
    ,jh.sql_severity
    ,jh.message
    ,jh.run_date
    ,jh.run_time
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
   ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
   ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE jh.run_status = 0
Scott Hodgin - Retired
  • 23,119
  • 2
  • 21
  • 43
1

  1. You can get failure reason by looking at message section of SQL-Agent Job history window.

  2. If the error produce more than 1024 characters, you can query msdb.dbo.sysjobhistory, to be specific to the requirement, you can use following query:

Declare @job_name sysname = 'YourJobName',
        @DateYYYYMMDD int = 20201030,
        @step_num int = 1;

select message
from msdb.dbo.sysjobhistory as jh
where   run_status = 0
    and step_id = @step_num
    and run_date = @DateYYYYMMDD
    and exists (select 1 from msdb.dbo.sysjobs where name = @job_name and job_id = jh.job_id)
  1. If the error produce more than 4000 characters, enable following setting from particular Job -> Step Properties (you can click View button for complete error message):

enter image description here

Shekar Kola
  • 2,271
  • 1
  • 7
  • 23
  • I did a test a while ago, and I found that the regular job output text is limited to 2061 characters, including the "Executed as..." stuff. This leaves about 2000 characters (not 4000) for the real messages. I also found that the job history GUI showed all of them (not 1024). – Tibor Karaszi Nov 03 '20 at 14:53
  • @TiborKaraszi, Maybe the particular message that you comparing only produced 2061 char length, try this query: `select MAX(LEN(message)) from msdb.dbo.sysjobhistory` hope you get the result 3999 (at some-point if your SQL Job step produce longer message). BTW, if the limit is 2000 char it must not accept 2061 chars, correct? – Shekar Kola Nov 03 '20 at 15:02
  • I did a test with more characters in the output, to explicitly test the limit. This was from a T-SQL jobstep, though. Perhaps you see a higher limit for some other step type? I was probably unclear: the limit is 2061 according to my test, but if you substract stuff that Agent adds such as "executed as...", you get *roughly 2000 characters left for test produced by the command(s) you executed. – Tibor Karaszi Nov 03 '20 at 15:12
  • @TiborKaraszi, Just for reference..[my test](https://i.stack.imgur.com/Mdqcx.png) :-) – Shekar Kola Nov 03 '20 at 15:37
  • I just re-did my test, and it was a single PRINT output that was cut off to 2160. Having more than one PRINT allowed me to get to 3998 in sysjobhistory. SSMS presented 3933 of those characters in the job history output GUI. – Tibor Karaszi Nov 03 '20 at 16:12