locked
How to retrive error message for sql agent job failure RRS feed

  • Question

  • My sql server agent job failed and didn't store any error message in the job history. Is there any other table from where I can get the error info????

    The job has sql_message_id = 16389 and sql_severity = 16. What does they mean????

    • Changed type Tom Phillips Tuesday, December 23, 2014 6:49 PM
    Tuesday, December 23, 2014 4:38 PM

Answers

All replies

  • this link will solve your problem

    http://www.sqlservercentral.com/articles/SQL+Server+Agent/67726/


    ebro

    CREATE PROCEDURE pr_GetStepFailureData
    (
    @JobName VARCHAR(250)
    )
    AS
    /*
    This procedure gets failure log data for the failed step of a SQL Server Agent job
    */
    DECLARE @job_id UNIQUEIDENTIFIER
    SELECT @job_id = job_id FROM dbo.sysjobs WHERE [name] = @JobName
    SELECT 'Step ' + CAST(JH.step_id AS VARCHAR(3)) + ' of ' + (SELECT CAST(COUNT(*) AS VARCHAR(5)) FROM dbo.sysjobsteps WHERE job_id = @job_id) AS StepFailed,
    CAST(RIGHT(JH.run_date,2) AS CHAR(2)) + '/' + CAST(SUBSTRING(CAST(JH.run_date AS CHAR(8)),5,2) AS CHAR(2)) + '/' + CAST(LEFT(JH.run_date,4) AS CHAR(4)) AS DateRun,
    LEFT(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)),6),2) + ':' + SUBSTRING(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)),6),3,2) + ':' + LEFT(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)),6),2) AS TimeRun,
    JS.step_name,
    JH.run_duration,
    CASE
    WHEN JSL.[log] IS NULL THEN JH.[Message]
    ELSE JSL.[log]
    END AS LogOutput
    FROM dbo.sysjobsteps JS INNER JOIN dbo.sysjobhistory JH
    ON JS.job_id = JH.job_id AND JS.step_id = JH.step_id
    LEFT OUTER JOIN dbo.sysjobstepslogs JSL
    ON JS.step_uid = JSL.step_uid
    WHERE INSTANCE_ID >
    (SELECT MIN(INSTANCE_ID)
    FROM (
    SELECT top (2) INSTANCE_ID, job_id
    FROM dbo.sysjobhistory
    WHERE job_id = @job_id
    AND STEP_ID = 0
    ORDER BY INSTANCE_ID desc
    ) A
    )
    AND JS.step_id <> 0
    AND JH.job_id = @job_id
    AND JH.run_status = 0
    ORDER BY JS.step_id

    -----

    EXEC pr_GetStepFailureData 'JobName'

    • Edited by ebrolove Tuesday, December 23, 2014 4:57 PM more info
    Tuesday, December 23, 2014 4:54 PM
  • My sql server agent job failed and didn't store any error message in the job history. Is there any other table from where I can get the error info????

    The job has sql_message_id = 16389 and sql_severity = 16. What does they mean????

    Refer the below link and use the script to get the job details.

    https://gallery.technet.microsoft.com/T-SQL-Script-Get-Job-231229b4

    Check the below link for more info on the 16389

    http://sqlblog.com/blogs/merrill_aldrich/archive/2010/06/22/long-i-os-and-tcp-timeouts-are-not-a-sql-server-problem-except-when-they-are.aspx

    • Marked as answer by naveej Tuesday, December 23, 2014 6:57 PM
    Tuesday, December 23, 2014 5:19 PM
  • see if this helps?

    http://www.sqlservercentral.com/articles/SQL+Server+Agent/67726/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, December 23, 2014 6:06 PM