tsql scheduled job info

Posted on 2009-02-08
Last Modified: 2012-05-06
Im using stored procedure sp_help_jobhistory to identify failed jobs and email them to me. i would like to include the step that failed and the message from the job history but im not sure where this info is stored ?
Question by:9772885
    LVL 31

    Expert Comment

    by:James Murrell
    unsure. but i get the jobs to write everything to file: and attach the file with the email
    LVL 31

    Expert Comment

    by:James Murrell
    LVL 40

    Accepted Solution

    check these scripts.
    -- Failed jobs report. This query lists the name of all jobs that failed in their last attempt which are enabled:
    SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0 and enabled = 1
    -- Disabled jobs. ever have a critical job that someone decided to disable? Catch it with this script!
    SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name
    ------ Job information
    SELECT AS job_name
           convert(nvarchar(10),(b.last_run_duration/10000)) + ':' + 
           convert(nvarchar(10),((b.last_run_duration%10000)/100)) + ':' + 
           convert(nvarchar(10),(((b.last_run_duration%10000)%100)%100)) as duration,
      FROM MSDB.dbo.sysjobs AS a
      JOIN MSDB.dbo.sysjobservers AS b ON a.job_id = b.job_id
      JOIN MSDB.dbo.syscategories AS c ON a.category_id = c.category_id
      JOIN MSDB.dbo.sysjobschedules AS S ON A.job_id = s.job_id
      JOIN MSDB.dbo.sysschedules_localserver_view AS v ON s.schedule_id = v.schedule_id
     WHERE ( <> 'Report Server' ) --AND LIKE 'Mars%'
     ORDER BY job_name 

    Open in new window

    LVL 25

    Expert Comment

    Can you not just create an operator and notify on fail using the Advanced properties of the job step?

    Author Comment

    ive had a look at the link which is very useful and although i can see i way of tracking individual steps on whether they are executing i cant see one for the overall job. I guess i could assmue that if any of the steps are executing then the job is but it would be nice of this was summerised somewhere else?

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction: Sometimes when I receive a call from my users to solve their problems it is very difficult for me to found their computer IP address. Even finding their computer Host to provide remote support can be a problem.  So I resorted to Goo…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now