Post reply

Job Step History - Failure not specified

  • July 27, 2011 at 8:22 am

    #240595

    I had a job step fail, but it seems as though any useful information is not captured in the step history, and nothing useful in the event log. Is there a way to increase the amount of information stored in the job step history ?

    For example, here's the information in the job step, but it doesn't list any reason for the failure .... looks as though it got truncated.:

    Message

    Executed as user: MyDomain\MyAccount. ...ET operation. [SQLSTATE 01003] (Message 8153) *** RCCD Automated End of Day *** [SQLSTATE 01000] (Message 0) automation.usp_RunEOD Started: 2011-07-27 03:44:38.380 [SQLSTATE 01000] (Message 0) STEP 1: Variable Declaration [SQLSTATE 01000] (Message 0) DB_Name1: RCCD_Production [SQLSTATE 01000] (Message 0) DB_Name2: RCCD [SQLSTATE 01000] (Message 0) STEP 1: Complete [SQLSTATE 01000] (Message 0) STEP 2: Loop through Active Campaigns [SQLSTATE 01000] (Message 0) Campaign_ID: A4DB4E9E-03CC-4534-9E2B-0384D147632E [SQLSTATE 01000] (Message 0) List_ID: 16755 [SQLSTATE 01000] (Message 0) Table_Name: DL_20110721_16755_CA [SQLSTATE 01000] (Message 0) STEP 2a: Variable Declaration [SQLSTATE 01000] (Message 0) DB_Name1: RCCD_Production [SQLSTATE 01000] (Message 0) DB_Name2: RCCD [SQLSTATE 01000] (Message 0) Max_Attempt: 5 [SQLSTATE 01000] (Message 0) Max_Live: 7 ... The step failed.

  • 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 8 posts - 1 through 7 (of 7 total)

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