Returning full error details from SQL Server Agent jobs

  • Comments posted to this topic are about the item Returning full error details from SQL Server Agent jobs

  • Nice article.

    I thought I could use the sysjobsteplogs table to get more information about agent job errors (without having to go to the log directory and parsing individual log files) but found that I couldn't rely on the log details always being written away there.

    If you are using Maintenance plans (that in turn generate SQL Agent jobs) then subsequently go in and amend the agent job step (via the advanced tab) to log to the table, then amend the maintenance plan again - the 'log to table' check box is cleared.

    If someone forgets to manually go back and change all affected agent jobs after a maintenance plan is updated the job logs stop getting written away to the msdb database.

    For info, the 'log to table' check box on the advanced tab switches on the msdb.dbo.sysjobsteps.flags column bitwise value 8.

    Manually created SQL Agent jobs are OK, has anyone else noticed this behaviour? I've tested the resetting issue on SQL 2005 and 2008 (latest service packs).

  • Hi Spencer,

    Thanks for the tips on maintenance plans, I had not encountered this and it is well worth knowing.

    Adam

  • Hi Adam,

    I thought this article started off great, very clear, well written etc.....but it seemed to suddenly just stop with the sql script.

    If you'd 'followed through' to the end, it would have been excellent.

    Regards,

    David.

  • Sounds like a lot of extra work. Why not just setup a log file on the job step advanced tab. Takes two seconds and will provide any error details you need.

  • We tried writing job step history to log files. We ran into problems when the job had to run under a certain account that didn't have write access on the server. In those cases we wrote the history back to the table.

  • The main issue with using the "Log to table" option without the "Append" option is that each execution overwrites the previous output. If you have a job that executes every so often and it fails just once overnight and subsequently succeeds, your failed output is lost forever. It would be nice to just have the full output for each step in the history until the history is purged. We put triggers on these tables to automatically send emails when failures occur including the full output, so it cannot be lost.

  • The rest of the article is inside the SQL script. Somebody forgot to close a tag somewhere.

  • Agreed on the missing tag... I believe it is a PRE tag that is missing.

  • aha....so it really did just stop with the sql script.

    Apologies to the author...I'll read again!

  • Hi David et al,

    I am the one who owes apologies, I have no idea how the tagging corrupted the article presentation, and I am sorry that this happened.

    I will attempt to rectify this when I can, but it might be a couple of days or so.

    Thanks!

    Adam

  • You can do a quick fix - though not normally recommended to amend system tables, but the following sets all agent jobs to log to table.

    UPDATE [msdb].[dbo].[sysjobsteps]

    SET flags = 15

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • If you happen to have a large number of jobs and want to change them all according to this topic; the script below should get the job done without having to manually click through each job/jobstep.

    declare @Jobs table (jobId uniqueidentifier)

    declare @Steps table (stepId int)

    declare @jobId uniqueidentifier,

    @stepId int

    insert into @Jobs select job_id from msdb.dbo.sysjobs

    while exists(select 1 from @Jobs)

    begin

    select top 1 @jobId = jobId from @Jobs

    delete from @Steps

    insert into @Steps select step_id from msdb.dbo.sysjobsteps where job_id = @jobId

    while exists(select 1 from @Steps)

    begin

    select top 1 @stepID = stepId from @Steps

    exec msdb.dbo.sp_update_jobstep @job_id=@jobId, @step_id=@stepId, @flags=8

    delete from @Steps where stepId = @stepId

    end

    delete from @Jobs where jobId=@jobId

    end

  • I am on SS 2000, can't get step 6! Any help to debug a job that failed?

  • The better way to set this is via

    UPDATE [msdb].[dbo].[sysjobsteps]
    SET
          Flags = 22,
          Output_File_Name = 'D:\SQLAgentJobLog\AgentOutput.txt'
    -- 15 checks Log to table box
    -- 16 checks Append output to existing entry in table ( if not set you lose the error the next time it happens )
    -- 20 checks Include step output in history

    -- 22 checks Append output to existing file ( so you have a log of all of the outcomes in D:\SQLAgentJobLog\AgentOutput.txt )

    Doug

Viewing 15 posts - 1 through 15 (of 15 total)

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