SAP PI database is growing larger

Objective

SAP process Integration database or connected proxy system size is growing large (tables: SXMSCLUP, SXMSCLUR etc.)  and you have scheduled the required deletion and archiving jobs, but some or all messages are neither deleted nor archived and the corresponding database tables increase. This blog post will help you to troubleshoot the Archive/Deletion process in the Integration Engine of SAP PI.

Preliminary Checks

ABAP reports RSXMB_SHOW_REORG_STATUS & RSXMB_SHOW_STATUS provide an overview for  all XML messages in XI persistence layer

Check Job status RSXMB_DELETE_ARCHIVED_MESSAGES

Concept

Let me start with a few words on archiving in context of PI, so we can have a better understanding on what is occurring and what we can do next

Archiving in PI works in 2 phases

(A) Write phase: messages are read from PI database and are written to archive data files on file system

(B) Deletion phase: messages are read from previously written archive data files in order to ensure data Integrity; if successful, messages are deleted from PI database

In order to trigger deletion (B) for existing archive files, manually run transaction SARA for archiving object BC_XMB, click button “Delete” and select a session or file. Enter start date and spool parameters (for Example, LP01) and press F8. You may also want to check KBA ##1603376.

To start deletion (B) automatically for future archive sessions run transaction AOBJ, double-click archiving Object BC_XMB in the list and change to “Customizing Settings” in the left hand tree. In frame “Settings for Delete Program”, choose option “Start automatically”.

In case a popup appears when saving the change asking for a package name, enter SXMSPERSIST you may also want to check KBA ##1603338

 That is, the deletion phase (B) must be executed at any rate (either manually or automatically). If you activated the deletion (B) by selecting “Start Automatically” no further, manual action is required as the system takes over the task of starting the deletion jobs for each archive data file.

If the deletion phase (B) is not executed for, whatever reason the next archive run will again handle the very same messages plus the new messages, see ## 872388 -> 1.d). Therefore, the answer to your question is:

If the deletion phase (B) cannot be executed anymore (because the archive data files have been deleted meanwhile), simply perform another archive run. This will again store the messages to a new archive data file and for that new file

How to archive smaller chunks of PI Integration Engine messages in Simple Steps please reference to the SAP note 1883012

1)t-code SM37 -> cancel the standard archiving job SAP_BC_XMB_ARCHIVE<client> (including ARV_BC_XMB_WRP* and ARV_BC_XMB_DEL*)

2)t-code AOBJ -> archiving object BC_XMB -> “Settings for Delete Program” -> option “Start Automatically” = > check

3) t-code AOBJ -> archiving object BC_XMB -> option ‘Do Not Start Before End of Write Phase’ => uncheck

4) t-code SXMB_ADMIN -> Configuration -> Define Interfaces for Archiving and Retention   Periods -> Retention Periods -> Asynchronous XML Messages without Errors Awaiting Archiving => for example, 400 days(Customer can run report SXMB_SHOW_REORG_STATUS in SE38 to judge if the retention period setting is proper or not.)

5) t-code SE38 -> report RSXMB_ARCHIVE_MESSAGES -> create Variants (for example, 00-19, 20-39, 40-59, 60-79, 80-100)as suggested in note 1883012 -> execute report SXMB_ARCHIVE_MESSAGES again and again with these variants

6) After finishing this round of archiving, please decrease retention period again, for example, to 360 days. Then perform the step 5) again.

7) Then decrease the retention period and schedule RSXMB_ARCHIVE_MESSAGES until the initial retention period is reached. (So from initial 400 days to 360 days … until you get to your required retention period of 30, 10, or 1 days).

We need to ensure that each archiving report is finished successfully. There should be enough space on file system. The archived files on file system cannot be deleted or moved until the related report RSXMB_ARCHIVE_MESSAGES finishes successfully and messages have been deleted automatically from DB.

Make Certain

Please ensure that each archiving report is finished successfully. There should be enough space on file system. The archived files on file system cannot be deleted or moved until the related  report RSXMB_ARCHIVE_MESSAGES finishes successfully and messages have been deleted automatically from DB.

Reference 

SAP Wiki Troubleshooting for Archive and Delete on Integration Engine

872388 – Troubleshooting Archiving and Deletion in PI