SQLTeam.com | Weblogs | Forums

SQL Agent Job and Job Step History in SQL Server


#1

Hi Experts,

Is there any possible to view all the error history for a single job?

Ex. My SQL server jobs are failing often, I want to take the logs for more than 2 months back, but there is only last 10 or 12 days log only. I want to take the previous logs also. Is there any possibles?.

Please let me know if possible.

Thanks
Param.


#2

I think you would need to have increased the History Rows (Right Click SQL Agent - Properties : History) - i.e. BEFORE the activity you now want to review - I find that the default is very low, particularly if there are many steps in a job and I disabled it altogether and create my own SQL Job History Purge task

You could restore the MSDB database from your backups, to a different, temporary, name and retrieve the history from that - and then piece together with additional, earlier, backups that you recover - until you have the complete picture.


#3

Hi Kristen,

Thank you so much. I increased the history rows.


#4

You might want to consider turning them off - and dealing with the Purging some other way. (Note that [unless it has changed] JUST setting the Purge Time in the Properties does NOT set up a scheduled task :frowning: all it does [or: used to do] was a one-time purge. Which i think is Nuts, and probably a lot of people get suckered into thinking they've set up a scheduled cleanup-task).

If you create your own task you can set a short time interval for very frequent tasks, and a longer period for all the rest. Mine is set to keep for 90 days by default, but my 10-minute backup routine only keeps for 3 days.


#5

Sure... Thanks Kristen :slight_smile: