sysjobhistory does not old older records

this is in sql 2000...

i basically want to see when was the last time a DTS package was run successfully which would be a year ago..

when i checked the above it has only 1000 entries.. is there a way i can look at the ones past the first 1000 records.. it is sorted by date desc..

thanks
LVL 6
anushahannaAsked:
Who is Participating?
 
Rich WeisslerConnect With a Mentor Professional Troublemaker^h^h^h^h^hshooterCommented:
@ScottPletcher - Thanks.  The problem in this case, sysjobhistory was being truncated to 1000 lines, which is the default.  I mentioned sysjobsteps as a possible location of last resort to get a possible last run date, because it does seem to store that information there as well in some instances.  Given that the information anushahanna is looking for is not in sysjobsteps, and has already been truncated from sysjobhistory - are you aware of ANOTHER different location this information might reside?
0
 
Scott PletcherSenior DBACommented:
Yes, there's a default option that limits the job to 1000 entries.  You can change that to increase it to whatever you want.
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Check for the default limit under the properties of the SQL Server Agent Properties.
Unfortunately the ones that have already been truncated are gone.

JobHistoryLimit.JPG
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
anushahannaAuthor Commented:
thanks for showing how to increase the limit..

now that i can't find through sysjobhistory, is there any other option to get a hint when it might have been run last?
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
If you don't care success/failure of the last run -- you can run:
select last_run_date from sysjobsteps where step_name='<jobname>'
It outputs as YYYYMMDD (their is a last_run_time field as well, if you need that.)
0
 
anushahannaAuthor Commented:
Rzamus, it seems like the DTS package was never setup as a job.. so I do not see it in sysjobsteps... does SQL keep track when DTS packages are run manually..?
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
I looked through all the DTS package tables yesterday, and didn't see anywhere where it tracked the run dates separately.  At best it has the various saved versions when the packages are modified/saved.
0
 
Scott PletcherSenior DBACommented:
sysjobhistory stores job *and* step history, so all job history goes into that table.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
No, there is no other standard location where past history is stored.

The only possibiity would be to recover the job history from the msdb tran log and/or tran log backups, assuming they were all available.
0
 
anushahannaAuthor Commented:
i gave a try with msdb TLB but too late.

at least now we have a better threshold (than 1000)

thanks very much for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.