Returning full error details from SQL Server Agent jobs

Question

Returning full error details from SQL Server Agent jobs

Adam Aspin

SSCarpal Tunnel

Points: 4900
More actions
September 9, 2009 at 12:09 am

#214216

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

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

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

LythamsFinest SSCommitted Points: 1786 More actions · Answer 1

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).

Adam Aspin SSCarpal Tunnel Points: 4900 More actions · Answer 2

Hi Spencer,

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

Adam

David McKinney SSChampion Points: 10472 More actions · Answer 3

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.

jcunningham Old Hand Points: 397 More actions · Answer 4

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.

jpertell SSC-Addicted Points: 459 More actions · Answer 5

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.

Daniel Ruehle SSC-Addicted Points: 411 More actions · Answer 6

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.

D. Davis SSC Enthusiast Points: 109 More actions · Answer 7

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

Jonathan Franzone Valued Member Points: 67 More actions · Answer 8

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

David McKinney SSChampion Points: 10472 More actions · Answer 9

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

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

Adam Aspin SSCarpal Tunnel Points: 4900 More actions · Answer 10

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

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 11

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]

Kyle Van Andel SSC Enthusiast Points: 140 More actions · Answer 12

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

saidbouzelha Grasshopper Points: 13 More actions · Answer 13

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

SQLOzzie Default port Points: 1441 More actions · Answer 14

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