Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Returning full error details from SQL Server Agent jobs Expand / Collapse
Author
Message
Posted Wednesday, September 9, 2009 12:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:03 AM
Points: 81, Visits: 903
Comments posted to this topic are about the item Returning full error details from SQL Server Agent jobs
Post #784717
Posted Wednesday, September 9, 2009 4:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 4:38 AM
Points: 1,518, Visits: 873
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
Posted Wednesday, September 9, 2009 4:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:03 AM
Points: 81, Visits: 903
Hi Spencer,

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

Adam
Post #784781
Posted Wednesday, September 9, 2009 6:05 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:02 AM
Points: 648, Visits: 1,874
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
Posted Wednesday, September 9, 2009 6:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 6:54 AM
Points: 212, Visits: 158
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
Posted Wednesday, September 9, 2009 7:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:33 AM
Points: 235, Visits: 496
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
Posted Wednesday, September 9, 2009 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 12, 2014 11:48 AM
Points: 161, Visits: 86
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
Posted Wednesday, September 9, 2009 7:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, September 9, 2009 8:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, September 9, 2009 8:54 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:02 AM
Points: 648, Visits: 1,874
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 »

Add to briefcase 12»»

Permissions Expand / Collapse