SQL Server – Query to View Failure Detail for SQL Agent Job Step

sql serversql server 2014

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 :

I know the job name.
I know when the job failed.
I know the step number

Best Answer

You can get failure reason by looking at message section of SQL-Agent Job history window.
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)

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):

Related Solutions

Sql-server – SQL agent job, why is the job history showing that a step is still running even though the job has completed

On the Hey Scripting Guy! blog at Technet, Chad Miller reports that if you run a Windows Powershell command as a SQL Agent job, and there are no syntax errors yet, the command produces an error (for example, attempting to get operating system information from an unavailable server). The SQL Server Agent job will report success. Check tip #10 in the post for instructions on how to implement error handling with different options so that you can, for instance, have SQL Agent continue on error or halt the Agent job. The reported "succeeded" may not in fact have succeeded.

10-tips-for-the-sql-server-powershell-scripter

SQL Server – Bulk Change Agent Jobs to Quit on Failure

Use Script to update Job steps In Bulk

USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO


    DECLARE  @job_id VARCHAR(1000),
             @Step_id INT

    DECLARE JobList CURSOR  
            FOR  

            SELECT  jb.job_ID,
                    jstp.step_id
            FROM    msdb.dbo.sysjobs jb
                    INNER JOIN msdb.dbo.sysjobsteps jstp ON jb.job_id = jstp.job_id
            WHERE   name = 'Jobname' 
            --AND Step_name <>'Step Name'     Add Step name to filter
            ORDER BY jb.job_ID, jstp.step_id


    OPEN JobList
        FETCH NEXT FROM JobList INTO @job_id,@Step_id
        WHILE @@FETCH_STATUS = 0
            BEGIN

                     -- Execute SP sp_update_jobstep to update Job Steps and pass 2(Quit with failure) as value to parameter @on_fail_action
                    EXEC msdb.dbo.sp_update_jobstep @job_id = @job_id, @step_id = @Step_id, @on_fail_action = 2 ---- 2 Quit with failure.


                FETCH NEXT FROM JobList INTO @job_id,@Step_id
           END
        CLOSE JobList
        DEALLOCATE JobList

Note: Make sure all steps need to be updated on failure as "Quit the job reporting failure" otherwise filter them by adding in WHERE Clause

Reference Link : https://msdn.microsoft.com/en-us/library/ms189827.aspx

Best Answer

Related Solutions

Sql-server – SQL agent job, why is the job history showing that a step is still running even though the job has completed

SQL Server – Bulk Change Agent Jobs to Quit on Failure

Related Question