Post reply

Job Step History - Failure not specified

  • July 27, 2011 at 8:30 am

    #1359305

    Do you have a job output file configured for the step. That would capture some useful information.

    M&M

  • July 27, 2011 at 8:37 am

    #1359312

    Here's how I've setup my jobs to get the full output and how you can see the output once it's captured.

    From the GUI

    Read the output from a query :

    SELECT * FROM msdb.dbo.sysjobstepslogs

    To activate all those outputs with a script (for all your jobs and all your steps) :

    1 - Change the options in the GUI and hit script.

    My output was this :

    USE [msdb]

    GO

    EXEC msdb.dbo.sp_update_jobstep @job_id = N'fe5166a3-fa18-4633-b7bf-ae2e5d80a722' ,

    @step_id = 1 , @flags = 20

    GO

    USE [msdb]

    GO

    EXEC msdb.dbo.sp_update_jobstep @job_id = N'fe5166a3-fa18-4633-b7bf-ae2e5d80a722' ,

    @step_id = 2 , @flags = 20

    GO

    Note that the important part is @flags = 20. I have tested this only on 1 system so please confirm the setting you need for your requirements.

    Here's the script to generate the code to apply that setting for all jobs & all steps (replace 20 with whatever you need).

    SELECT

    job_id

    , step_id

    , step_name

    , flags

    , 'EXEC msdb.dbo.sp_update_jobstep @job_id=N'''

    + CONVERT(VARCHAR(36) , job_id) + ''', @step_id = '

    + CONVERT(VARCHAR(30) , step_id) + ' , @flags=20' AS Cmd

    FROM

    msdb.dbo.sysjobsteps

    WHERE

    flags <> 20

    ORDER BY

    job_id

    , step_id

    Copy, paste, run.

    I had to use this script because I had like 100 steps to change!

  • July 27, 2011 at 8:51 am

    #1359333

    Thanks !!

  • July 27, 2011 at 8:57 am

    #1359342

    homebrew01 (7/27/2011)

    Thanks !!

    Do you think it's worth converting this to a sparkle?

  • July 27, 2011 at 9:08 am

    #1359358

    Sparkle ?

  • July 27, 2011 at 9:12 am

    #1359360

    homebrew01 (7/27/2011)

    Sparkle ?

    Or Spakle... mini article. Something people need to learn but doesn't require the full 300-500 words.

  • July 27, 2011 at 9:20 am

    #1359368

    Sure .... lots of little tips out there that could be useful to others.

Viewing 7 posts - 1 through 8 (of 8 total)

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