I have a job with over 226690 records in the logevent table. When I attempt clear job history the adtempus console hangs. I assume that adtempus is attempting to delete the history. Is there anyway to manually clear the history records?
SQLServer 2005
Win 2003
Adtempus Product Version: 3.0.8
Thanks
I moved your post from its original location as a comment on a help topic.
You can clear the history by updating the database directly, but it would be best to first understand why there are so many records. Was there a problem with history retention settings, or is something else going on? If your job is using file conditions, you could be experiencing this problem. In that case, please open a support case so we can get a fix to you.
Are there also a large number of history records for the job (in the executionHistoryItem) table? If so it would be best to open a support case so we can see if there's a problem that's preventing history data from being purged properly.
Otherwise you can query the records to get some idea of what's happening (see below).
See this article for information on how to query/update the database. If possible, stop the adTempus service before proceeding. To begin, you first need to get the identity value (OID) for the job. Use this query:
select name, OID from job
In the results, locate your job and copy the OID value. Use it in the subsequent queries wherever "{OID}" appears
Use this query to get a summary of which message(s) are getting logged excessively:
select messageID,count(*)
from logevent
where joboid='{OID}'
group by messageID
order by count(*) desc
Let me know which message ID(s) are occurring most; that will tell us more about what's going on.
To delete log messages, use this command:
delete from logevent where jobOID='{OID}' and messageTimestamp<'2013-12-01'
(adjust the date to suit your needs)
We have over 100 jobs configured and at some point the retenion was set to indefinate. We went back and set the retention to 30 days, but the history does not clear. I have been attempting to clear the history and then set I was going to work with the retention setting to determine what the problem is. We had aprox 6G of log data. I have been able to get it down some.
I found that if I duplicate a job, then go back and delete the orginal job, the history appears to "clean up" after a day or so.
Just today I did that for the jobs with records over 6000.
It would be easier to just delete the history manually with a query. I see from the activity montior that a curser delete is being utilized. But it eventually errors. That is when I started using the duplicate trick.
here is where I am right now. The system has not yet purged the history for all the deleted jobs.
dbase TabName Rows ReservedMB DataMB Index_SizeMB UnusedMB
adtempus_ENGWEB01-F logEvent 129030 903.445 531.344 336.680 35.422
adtempus_ENGWEB01-F executionHistoryStep 350340 714.375 584.570 128.477 1.328
adtempus_ENGWEB01-F capturedFile 293414 507.125 378.453 121.664 7.008
adtempus_ENGWEB01-F executionHistoryItem 43774 278.508 157.227 67.711 53.570
If I had a query that would delete all records and not leave any broken relationships, that would enable me to manually delete the history. I have not been able to determine the relationship between the various tables.
If such a query exists, it would be appreciated. Hopefully, the copy trick will get me to a working point.
attached is a summary of the history retention for all the jobs. The NULL names are jobs I have copied and deleted and am hoping will be cleaned up
select count(*) as i, joboid ,( select name from job where job.oid=joboid)as name from logevent group by joboid order by i desc
Once you change the retention settings, the history should get purged in the next cleanup cycle, which happens roughly every 12 hours. However, we have seen some cases with scenarios like yours, where the retention settings change and adTempus suddenly has a lot of data to clear, where it runs into problems doing the clearing, sometimes due to database deadlock issues.
As you say, the easiest solution is to just clear the records directly. Assuming you want to purge everything from before December 1, 2013, for all jobs, you need to execute the following:
delete from logevent where messagetimestamp < '2013-12-01';
delete from executionhistorystep where owner in (select oid from executionhistoryitem where executionstart<'2013-12-01');
delete from executionhistoryitem where executionstart<'2013-12-01';
delete from capturedfile where owner not in (select oid from executionhistoryitem) and oid not in (select oid from executionhistorystep);
Be sure you stop the adTempus service before you execute these updates.
The captured files themselves are stored on disk, but adTempus should clean them up during its next scavenging cycle, which should start within 4 hours after you restart the service.
Thanks,
the explaination and query are what I was looking for.
I will give it a try
Thanks!
Replies are disabled for this topic.
Staff