This is a little old-school, but still a very relevant tip. This problem has been around for all of the ALI 6.x days (including 6.5), and if I remember correctly, even the Plumtree 5.x days. Basically, the automation server logs its activity to the PTJOBLOGS table, and if you've got jobs that are logging in verbose or very frequently, this table can become HUGE: I had a recent customer whose database had grown to over 80 GB because of this table.
Basically, to prevent this table from growing astronomically big, make sure:
- You don't have jobs that are running way too often (once a minute typically means a job is running - and logging - constantly)
- You don't have verbose logging for any jobs running in the portal, and
- That your portal configuration is set to only save a reasonable amount of job log data. By default, the portal will keep around 60 days worth of logs, which is a pretty big number. If you ask me, any job logs older than 7 days are worthless because jobs always run more than once a week. But, of course, you're not asking me, so I'll just tell you how to change this setting and you can decide for yourself. The configuration setting isn't available through the UI; instead you have to tweak the database. Specifically, you have to update the PTSERVERCONFIG table in the ALI database with SETTINGID=15. Set the value to whatever you think is appropriate based on your use of the job log:
Finally, what happens if you've already got a PTJOBLOGS table that contains 200 million rows? Here's a tip that I got from our friend and client, Mike Jones at MedSolutions, Inc.: if you run an amateur update like "delete * from ptjoblogs", the update will take forever because all transactions will be logged. If you run "truncate table ptjoblogs", on the other hand, you're just dropping the table and don't have to wait ridiculous amounts of time for the update to happen (not to mention the amount of database log space you'd be consuming).
Leave a comment