Loading ...
Sorry, an error occurred while loading the content.
 

Re: Process Scheduler Tables Growing Rapidly

Expand Messages
  • James Blanding
    Those tables store the information that appears in Process Monitor when you drill down into the Process Details - Message Log page. If you simply truncate
    Message 1 of 2 , Sep 29, 2004
      Those tables store the information that appears in Process Monitor
      when you drill down into the Process Details -> Message Log page. If
      you simply truncate the tables, you will lose that information. But
      we noticed that many of the rows in these tables were orphaned; that
      is, the processes that created them no longer existed in the
      PSPRCSRQST table. On our year old production system, there were many
      more orphaned rows than non-orphaned rows.

      We run 8.42 currently, and the internal Purge process does nothing to
      clean these up. Supposedly they redesigned the Purge process in 8.44
      to clean up these tables...I guess we'll find out soon, as we're
      upgrading to 8.45 right now.

      But anyway, our solution in 8.42 is to have a custom PL/SQL program
      run daily that suppliments the functionality of the internal Purge
      process. The part of the program that deals with the Message Log
      tables simply deletes up to a specified number (ml_max_rows) of
      orphaned rows from each of these tables. These are the relevant
      statements from the program:


      delete from ps_message_log
      where process_instance not in (select prcsinstance
      from psprcsrqst)
      and rownum <= ml_max_rows;

      delete from ps_message_logparm
      where process_instance not in (select prcsinstance
      from psprcsrqst)
      and rownum <= ml_max_rows;


      Since these statements won't affect a Queued process, we feel they're
      safe, but don't take my word for it. :)

      --James

      --- In psftdba@yahoogroups.com, "pjksel" <pjksel@y...> wrote:
      > Hi,
      >
      > I am using tools 8.43.12 and I would like to truncate the
      > ps_message_log and ps_message_logparm tables in production as they
      > are growing rapidly. According to most documents I have read, it is
      > ok to truncate these tables as long as you do not have any jobs
      that
      > are queued. We have several hundred recurrences setup which always
      > show a queued status. How can we truncate these tables without
      > removing or impacting the queued recurrences.
      >
      > Thanks
    Your message has been successfully submitted and would be delivered to recipients shortly.