Post reply

How to see failed jobs

  • March 10, 2008 at 5:37 am

    #203939

    Hi,

    plz tell me how can we see failed jobs through t-sql

    thaxxx

    regards

    jagpal singh

  • March 10, 2008 at 5:58 am

    #787293

    Here is a useful query off of Google. I modified to your needs to find the failed jobs.

    SELECT

    SJ.name 'JOB Name'

    ,'Run date : ' +

    REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,run_date)),102),'.','-')+' '+

    SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),5,2) 'Start Date Time'

    ,SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),5,2) 'Duration'

    ,CASE run_status WHEN 1 THEN '1-SUCCESS' WHEN 0 THEN '0-FAILED' ELSE CONVERT(varchar,run_status) END AS 'Status'

    ,Step_id

    ,[Message]

    ,[Server]

    FROM MSDB..SysJobHistory SJH

    RIGHT JOIN MSDB..SysJobs SJ

    ON SJ.Job_Id = SJH.job_id

    WHERE

    Step_ID = 0 --Comments this line if you want to see the status of each step of the job

    AND run_status = 0 -- Failed Job only flag

    ORDER BY run_date DESC, run_time DESC, step_ID DESC

  • March 10, 2008 at 6:08 am

    #787299

    Hi,

    Thanxxx its so nice query .its working perfect .but will u plz tell me where is

    FROM MSDB..SysJobHistory

    i cant find this sys.jobhistory

    regards

    jagpal singh

  • March 10, 2008 at 6:29 am

    #787316

    Systems Databases, msdb, tables, system tables

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply