SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Returning full error details from SQL Server Agent jobs


Returning full error details from SQL Server Agent jobs

Author
Message
Adam Aspin
Adam Aspin
SSC-Addicted
SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)

Group: General Forum Members
Points: 445 Visits: 1032
Comments posted to this topic are about the item Returning full error details from SQL Server Agent jobs
LythamsFinest
LythamsFinest
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1568 Visits: 879
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
Adam Aspin
SSC-Addicted
SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)

Group: General Forum Members
Points: 445 Visits: 1032
Hi Spencer,

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

Adam
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 2090
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
jcunningham
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 174
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
jpertell
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 557
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
Daniel Ruehle
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 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.
D. Davis
D. Davis
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 126
The rest of the article is inside the SQL script. Somebody forgot to close a tag somewhere.
Jonathan Franzone
Jonathan Franzone
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 11
Agreed on the missing tag... I believe it is a PRE tag that is missing.
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 2090
aha....so it really did just stop with the sql script.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search