Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 View job history

Author  Topic 

sravanim
Yak Posting Veteran

58 Posts

Posted - 2013-07-29 : 06:40:39
How to see the job history more than a week?

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-07-29 : 06:51:37
this may help
http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-29 : 07:04:05
You also have Job Steps Execution History report among standard list of reports available from SQL Management Studio by selecting SQL Server agent and choosing option Reports -> Standard Reports -> Job Steps Execution History

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-29 : 09:02:13
quote:
Originally posted by visakh16

You also have Job Steps Execution History report among standard list of reports available from SQL Management Studio by selecting SQL Server agent and choosing option Reports -> Standard Reports -> Job Steps Execution History

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Standard Job Steps Execution History report that I am familiar with show only 7 days activity. For periods earlier than that, you have to use something similar to what Tara's blog showed, or even right click on the jobs node under SQL Server Agent and view history.

Is there an option or setting that would let you configure the standard Job Steps Execution History to show more than a week's worth of data?
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-07-29 : 14:52:23
If you are using the default settings for SQL Server Agent - you may not even be able to go back that far for all jobs. The default settings only allow for a maximum of 1000 rows for the whole table - and 100 rows for individual jobs.

All it takes is one job running every 10 seconds to push your history out of the table.

The other option on that page (Agent Properties - History) is a one-time option. It runs the purge process when you close the window by hitting OK. To get the same functionality you can either create a maintenance plan with a single task (History Cleanup) and configure it for how much history you want to keep - or you can schedule a custom cleanup executing the procedure(s):

msdb.dbo.sp_delete_backuphistory
msdb.dbo.sp_purge_jobhistory
msdb.dbo.sp_maintplan_delete_log

Look in BOL for each procedures available parameters.
Go to Top of Page
   

- Advertisement -