Returning full error details from SQL Server Agent jobs

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Adam Aspin » Returning full error details from SQL Server...

14 posts, Page 1 of 2

1 2 »»

Returning full error details from SQL Server Agent jobs

Rate Topic

Display Mode

Topic Options

Author

Message

Adam Aspin

Posted Wednesday, September 09, 2009 12:09 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:37 AM
Points: 81, Visits: 849

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

Post #784717

LythamsFinest

Posted Wednesday, September 09, 2009 4:28 AM

UDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 8:37 AM
Points: 1,497, Visits: 811

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

Post #784772

Adam Aspin

Posted Wednesday, September 09, 2009 4:36 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:37 AM
Points: 81, Visits: 849

Hi Spencer,

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

Adam

Post #784781

David McKinney

Posted Wednesday, September 09, 2009 6:05 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601

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.

Post #784843

jcunningham

Posted Wednesday, September 09, 2009 6:17 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, March 22, 2013 6:26 AM
Points: 212, Visits: 149

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.

Post #784849

jpertell

Posted Wednesday, September 09, 2009 7:00 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 02, 2013 7:33 AM
Points: 216, Visits: 455

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.

Post #784880

Daniel Ruehle

Posted Wednesday, September 09, 2009 7:35 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 9:21 AM
Points: 158, Visits: 83

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.

Post #784935

D. Davis

Posted Wednesday, September 09, 2009 7:55 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 8:59 AM
Points: 45, Visits: 126

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

Post #784969

Jonathan Franzone

Posted Wednesday, September 09, 2009 8:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 9:55 AM
Points: 1, Visits: 11

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

Post #784994

David McKinney

Posted Wednesday, September 09, 2009 8:54 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601

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

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

Post #785047

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions