We have SAP PI 7.1
In case we have errors on Adapter Engine, and we have messages in status System Error, this message keep in database until then, until Cancelled.
This is the reason the growth table BC_MSG_AUDIT
For resolve this problem in Runtime Workbench via message monitoring we select messages with status System Error and press button Cancel. On question Are you sure you want to execute the action? We answer Apply.
Check the successful execution of the deletion and archiving jobs via the background processing monitor in the RWB. Enter the RWB, navigate to “Component Monitoring” and click “Display”. Choose the Adapter Engine of your Integration Server host and hit the button “Background Processing”.
On the next time when deletion job started messages with status Canceled will be deleted and will be deleted rows in table BC_MSG_AUDIT
More info in note below
Note 872388 – Troubleshooting Archiving and Deletion in PI
The database of your PI or connected Proxy system is growing and you would like to know how to remove messages from the database.
You have scheduled the required deletion and archiving jobs, but some or all messages are neither deleted nor archived.
Archiving, Deletion, SXMSPMAST, SXMSPMAST2, SXMSCLUR, SXMSCLUR2,
SXMSCLUP, SMXSLUP2, SXMSPFRAWH, SXMSPFRAWD, XI_AF_MSG, BC_MSG, workitem, ccBPM, XI, Proxy
If you need detailed information about deletion and archiving please consult http://help.sap.com.
For directly accessing the respective chapters, please use:
1. For messages and history entries in the Integration Engine http://help.sap.com/saphelp_nw73/helpdata/en/0e/80553b4d53273de10000000a114084/content.htm
2. For messages in the Adapter Framework:
Note 816022, question 8 (deletion) and http://help.sap.com/saphelp_nw73/helpdata/en/cf/b2fc3f48ecc742e10000000a1550b0/content.htm (archiving)
For workitems in the Business Process Engine: Note 836092
3. For Performance Data in the Integration Engine Note 820622 and http://help.sap.com/saphelp_nw73/helpdata/en/8b/
Looking at archiving and deletion in PI the following runtime items have to be considered:
XML messages and history entries in the ABAP Integration Engine
XML messages in the Java Adapter Framework
Workitems in the Business Process Engine (BPE)
Performance data collected in the Integration Engine
Per default only asynchronous messages (EO and EOIO) will be persisted on the ABAP and Java side. Synchronous messages (Best Effort) will only be persisted if an error occurs during processing or if the parameter LOGGING_SYNC (ABAP only) is set. Setting LOGGING_SYNC is not recommended to reduce processing overhead. Note: Also on applications systems connected to PI via ABAP proxies the messages will get persisted and archiving/deletion has to be configured. Performance data on the other hand are persisted for both, synchronous and asynchronous, messages (tables SXMSPFRAWH).
In general, there are for all data types in PI two ways to remove the relevant objects from the database – Deletion or Archiving. The exceptions are history entries and performance data which can only be deleted. Also synchronous PI messages can only be deleted and not archived.
Per default no deletion & archiving is carried out in the Integration Engine and BPE. Therefore manual interaction is necessary by the administrator of the PI system to setup a suitable deletion/archiving strategy. On the AFW messages will per default be deleted after a default time of 1 day (for 7.0x and lower the default is 30 days). Reorganization of PI internal processing data is vital for the performance of an PI system. Since many of the relevant flags are persisted on database during the processing time of a message and can not be changed easily afterwards Archiving and Deletion has to be configured prior to processing messages for the relevant interface. If you want to change the archiving of interfaces after the message processing please consult Note 789352.
In general only messages with a final status can be deleted. Asynchronous messages in error can not be deleted and have to be cancelled first. While in the Integration Engine (ABAP) asynchronous messages that were manually cancelled or changed have to be archived they will be per default be deleted directly in the Adapter Framework.
For the archiving, different actions need to be taken, depending on the type of object you would like to archive:
To archive interfaces in the Integration Server, the interfaces have to be defined for archiving via transaction SXMB_ADM -> “Define Interfaces for Archiving and Retention Periods”. These messages will only be removed from the database via the archiving procedure. Deletion procedures will not affect them.
To archive workitems, it is sufficient to plan archiving jobs as described in the link above. If a deletion job is running as well, it will delete workitems, i. e. there is no flag for workitems as for XML messages in the Integration Server.
To archive interfaces in the Adapter Framework (AFW), the XML DAS (Data Archiving Store) has to be set up. If no archiving has been set up XML message will be deleted from the AFW by the standard deletion job after a default time of 30 days. You can define which interfaces should be archived via archiving rules.
1. Troubleshooting Archiving / Deletion of XML messages and history entries in the Integration Server
Note: Only messages in a final status can be deleted / archived. Asynchronous messages with errors have to be cancelled first. After that these cancelled messages (asynchronous only) have to be archived and will be deleted!
a) Check if the deletion and archiving jobs have been carried out. Enter transaction SM37 and search for
– SAP_BC_XMB_DELETE_ (Deletion job for XML messages)
– SAP_BC_XMB_HIST_DELETE_ (Deletion job for history entries)
– ARV_BC_XMB_WRP (archiving, step 1)
– ARV_BC_XMB_DEL (archiving, step 2)
Alternatively, for deletion of XML messages and history entries, you can enter transaction SXMB_ADM -> Schedule Deletion Jobs and use the button “Jobs”.
For archiving jobs, you can alternatively use transaction SXMB_ADM -> Schedule archiving job and use the button “Job Overview”. Please note that you will select the prejobs only, i. e. the jobs that plan the final job. You will not find the final archiving jobs.
Note: All these jobs are also relevant for applications systems connected via ABAP proxies and should be scheduled on these systems as well!
b) Check the successful execution of these jobs. To get a first overview you can use SXMB_MONI -> Job Overview. In case of errors please use the job log of each of the jobs listed in step 1) for errors (use transaction SM37).
c) Only for archiving on IE: Use the overview of your archiving sessions to check if all archiving sessions are completed. To do so, start transaction SXMB_ADM -> Schedule Archiving Job. In there use button “Archive Management” and select the Object Name “BC_XMB” (it is selected by default). Then select the button “Management”.
d) Only for archiving on IE: To get an overview over the situation for your archiving run transaction SXMB_ADM -> Schedule Archiving Job -> Archive Management -> Management. The archiving always consists of two steps. First the message is written to an archive file and afterwards the messages will be deleted. The deletion can directly be done as postprocessing step of the writing phase or independently using SARA. Per default the messages are not deleted after archiving. To change this you have to change the customizing settings via transaction AOBJ. To do so select the customizing for object BC_XMB. In frame “Settings for Delete Program” select option “Start Automatically”. Note: If the deletion is not automatically started, the archiving job will write with each execution same messages (plus new ones) marked for archiving on the archiving directory (by default global directory).
e) Only for archiving on IE: An archiving session is handled as an transactional unit. In other words: whenever an error occurs during write phase the entire session is considered invalid. As a consequence all message will be archived again by the very next session. Archiving files resulting from erroneous sessions will not be deleted. This behaviour can be changed such that a single archiving file is handled as a transactional unit. I.e. when an error occurs while writing a file this error does NOT affect previously written files. For each complete file the post-processing (storing to archive server, deletion) will be triggered immediately after writing the file. In case you would like to do this change please call transaction AOBJ for archiving object BC_XMB and de-select option ‘Do Not Start Before End of Write Phase’.
f) To get an overview over the situation in your persistence layer, run report RSXMB_SHOW_REORG_STATUS. Chooose the option “Checking all messages” and execute the report. For details about this report, consult note 696738.
g) In the first paragraph (Overview) of the results of report RSXMB_SHOW_REORG_STATUS, check if the “Number of messages in db” differs from the “Number of messages in client”. If this is the case, then there are messages in additional clients. Please note that deletion and archiving jobs are client specific, thus you might have to schedule the jobs in the other clients as well if not done already.
h) Keep in mind that you have set retention times for messages (different values for synchronous and asynchronous messages). As long as the end of the defined retention time is not reached, the messages are neither deleted nor archived. To check the set values carry out transaction SXMB_ADM -> Define Interfaces for Archiving and Retention periods -> Retention Periods (button in the menu bar).
The following parameters are set for asynchronous messages:
– XML Messages Without Errors Awaiting Deletion (default = 1 day)
– XML Messages Without Errors Awaiting Archiving (default = 1 day)
The following parameters are set for synchronous messages:
– XML Messages with Errors Awaiting Deletion (default = 1 days)
– XML Messages Without Errors Awaiting Deletion (default = 0 days)
As already stated above synchronous messages without errors will per default not be persisted.
i) Keep in mind that you may have activated the table switch procedure. You can check this via transaction SXMB_ADM -> Configure delete procedure. The information button on that screen explains the switch procedure in detail. If the switch procedure is chosen, then the XML messages will only be removed (via table drop) if
A) the retention time is expired and
B) a specific fill grade of the table is reached.
The default value is 90, that is the deletion will occur when 90% of the table is filled. You can set this parameter via transaction SXMB_ADM -> Configure Integration Engine -> Specific Configuration. Create an entry of category DELETION, parameter DROP_MAX_TABLE_LOAD, no subparameter.
If you encounter performance problems after execution of the switch this might be due to deletion of the table metadata during the context switch. A solution for this problem is described in Note 1032733.
To monitor the Switch Procedure (Fill Level, Active Container) you can use transaction SXMB_MONI -> Persistence Layer Analysis. Please note: Based on the value chosen for DROP_MAX_TABLE_LOAD the “Current Fill Level” entry might show a red alert since the “Maximum Number of Table Entries” is exceeded. This is nothing to be concerned about.
If you choose to use the switch procedure, we recommend setting the parameter in a way that not too many messages are being copied. The recommendation is to set DROP_MAX_TABLE_LOAD in a way that at the point in time when the value of DROP_MAX_TABLE_LOAD is reached, the ratio between messages to be copied and the total number of messages is about 20%.
The best way to determine the correct parameter value is to multiply the rough number of messages processed per day with the number of days that these messages are being retained in the system (retention time, see section 6). The result of this is the number of messages that need to be copied at the point in time when the value of DROP_MAX_TABLE_LOAD is reached. As mentioned before it is recommended that this value corresponds to 20% of the total messages in the system. The total number of messages at this point in time is thus the calculated value for ‘messages to be copied’ times 5 (20% x 5 = 100%). The total number of messages divided by the maximum possible number of messages in the database, is the value that should be set for DROP_MAX_TABLE_LOAD. The maximum possible number of messages is determined via a DDIC function and can be found via transaction SXMB_MONI -> Persistence Layer Analysis -> value of entry ‘Maximum Number of Table Entries’.
Daily # of messages: 25.000
Retention time: 5 days
Max. # of table entries: 900.000
Messages to be copied during switch: 25.000 x 5 = 125.000
Total number of messages for the 20% ratio = 125.000 x 5 = 625.000
DROP_MAX_TABLE_LOAD = 625.000 / 900.000 = 70 [%]
Please keep in mind that during the execution of the switch additional table space is required to copy to the valid messages to the new master table. Example: Before the switch your system contains 5 millionen entries. 1 million of them are still in the retention period and thus have to be copied to the new master tables. Only after the copy job finished the original tables can be dropped. Therefore you temporarily need sufficient table space to save 6 Mio messages. If there is not enough space on the database to execute the copying of messages please use report RSXMB_SWITCH_DEL_OLD_ENTRIES as described in note 842187.
The status of the switch and copy job can be monitored via transaction SXMB_MONI -> Job Overview. In cause you encounter problems in the job execution these jobs can also be rescheduled directly from here.
j) Check if there are message with missing qRFC entries. This happens if a user deletes queue entries using transaction SMQ2 instead of (correctly) canceling messages via transaction SXMB_MONI. Messages without queue entries will remain in status “Recorded” and “Recorded for Outbound Processing” but will not be processed. Due to the Non-final status these messages can also not be deleted or archived. These messages can be restarted via SXMB_MONI. In case you would like to cancel them you can use report RSXMB_CHECK_MESSAGE_QUEUE as described in detail in Note 688147 or report RSXMB_CANCEL_MESSAGES. Keep in mind that deletion of qRFC entries is not recommended at all in productive SAP environment. Deleting queue entries which are currently getting processed can result in inconsistencies between the RFC runtime tables and XI runtime tables. In such a case you can use Note 779664 to check for inconsistencies in the tRFC tables.
k) Check status of messages:
As stated before only messages with a final status can be deleted or archived. To get an overview about the processing status and adapter status of a message you can use report RSXMB_SHOW_STATUS. For details about this report please refer to Note 944727.
k.1) Check if there are asynchronous messages in error status. Asynchronous messages in error status are neither deleted nor archived. This behavior is different to erroneous synchronous messages which get deleted after their retention period (see step h) has expired.
Asynchronous erroneous messages, however, must be cancelled first and will then be archived. Also here report RSXMB_SHOW_STATUS gives you an overview. Check the number of messages for the following status
– 014: System Error – Manual Restart Possible
– 017: Application Error – Manual Restart Possible
– 021: Canceled Manually
– 019: Changed Manually
Messages with status 014 and 017 have to be cancelled first. You can use transaction SXMB_MONI, search for erroneous messages and manually cancel them or you can use the report RSXMB_CANCEL_MESSAGES. SAP does not recommend scheduling this job in productive environment. If you use it to cancel messages in productive environment you have to make sure that the messages to be cancelled have been resend or that it is ok for business to cancel them.
As stated before manually cancelled or manually changed messages (status 019 and 021) have to be archived. Therefore you always have to configure archiving to handle manually edited messages. Should you decide (for non-productive systems only) that you would like to delete these manually cancelled messages, please refer to Note 820149.
To find out the meaning of the different message status provided by report RSXMB_SHOW_STATUS you can use the content of table SXMSMSTAT via transaction SE16.
k.2) Check if there are messages with an adapter status that does not allow for deletion or archiving. This is only valid if you use the outbound IDoc Adapter or ccBPM. Based on the results of report RSXMB_SHOW_STATUS check the number of messages with adapter status 001, 007 and 008. Should you find status 001 and 008, schedule report SXMS_REFRESH_ADAPTER_STATUS. This job is recommended to run regularly (see housekeeping jobs for PI in the online documentation). Also check for errors in transaction SM58 since this is also causing errors on the outbound adapter status for IDoc messages.
If you still see messages with a wrong adapter status after this, you have to check the status of your Integration Processes in the BPE. For more details please refer to SAP Notes 942651, 960240 and 1042379.
k.3) Check if there are messages with a white flag in transaction SXMB_MONI or with message state: 01 – Scheduled
If this is the case, please use report RSXMB_CANCEL_NO_COMMIT_MSG as described in Note 712628.
l) Check if there are message with a digital signature. These messages are archived by default. If you have defined no interfaces for archiving and have only set up the deletion procedure then these messages with a digital signature will not be removed from the database. Schedule archiving jobs to remove signed messages from the database. If you do not know if a digital signature is used for your messages, use transaction SE16 -> table SXMSPMAST (or SXMSPMAST2 if table switch is activated) and check for entries with value “1” in the field “SECURITY”.
m) Growing PI performance tables (SXMSPFRAWH and SXMSPFRAWD): Verify the execution of the program SXMS_PF_REORG which has to be scheduled in regular intervals. Details can be found in SAP Note 820622.
n) Growing PI history table (SXMSPHIST): Use report RSXMB_CHECK_HISTORY as described in Note 1113757 to verify the status of the records in table SXMSPHIST.
o) If the above checks do not help you to decrease your database size, please open an OSS messages, provide the details of your checks and attach the information that you gathered from the report RSXMB_SHOW_REORG_STATUS.
2. Troubleshooting Archiving / Deletion of workitems in the Business Process Engine
a) Check if the deletion and archiving jobs have been carried out. Enter transaction SM37 and search for jobs:
– that use the ABAP program RSWWWIDE (deletion)
– with the name ARV_WORKITEM_WRI (archiving, step 1)
– with the name ARV_WORKITEM_DEL (archiving, step 2)
– with the name RSWF_XI_INSTANCES_DELETE (see Note 874708)
b) Check if any errors occurred by clicking on the button “Job Log” in transaction SM37 for the jobs in step 1)
c) Only for archiving: Use the overview of your archiving sessions to check if all archiving sessions are completed. To do so, start transaction SXMB_ADM -> Schedule Archiving Job. In there use button “Archive Management” and select the Object Name “WORKITEM”. Then select the button “Management”.
d) In case you find messages in your PI belonging to ccBPM process instances that are no longer used or acknowledgment messages which can not be assigned to related process instances and are neither deleted nor archived please refer to Note 1042379 for troubleshooting information.
3. Troubleshooting Archiving / Deletion in the Adapter Framework
Note: Only messages in a final status can be deleted/archived. Messages with errors have to be cancelled first. Manually cancelled messages do not have to be archived (contrary to the Integration Server) but can also be directly deleted in the AFW.
Messages on the Java side are persisted in table BC_MSG (XI_AF_MSG for PI 7.0x and lower). Since this is a table on the Java DB schema it can not be monitored via transaction SE16.Starting from 7.1 it is possible to check the number of entries in these tables using the NWA OpenSQL monitors. For older releases you have to use database tools. To analyze the overall number of messages based on their status you could use the following SQL statement:
SELECT COUNT(*), STATUS FROM .BC_MSG GROUP BY STATUS;(XI_AF_MSG for older releases).
If you configured the Message Overview introduced in Note 1031773 you can see an overview of all status of all messages being processed after the activation.
To get more information about the different status of messages in the Adapter Framework please look at Note 813993.
a) Check the successful execution of the deletion and archiving jobs via the background processing monitor in the RWB. Enter the RWB, navigate to “Component Monitoring” and click “Display”. Choose the Adapter Engine of your Integration Server host and hit the button “Background Processing”. A new job configuration window will be opened displaying a list of background jobs executed in the Adapter Engine. Check the traffic light in front of the job description indicating possible problems during execution. Highlight the “Default Delete Job” or a customer named version of the deletion job and select the Log tab. Check for errors during execution and the number of deleted messages.
b) If you decided to archive, check that the archiving jobs have been carried out successfully. Also the archiving job can be monitored via the “Background Processing” via the “Component Monitoring” as described above.
c) Keep in mind that the default persist time for messages in the Adapter Framework is 30 days. Starting with 7.10 the default retention period was changed to 1 day to minimize DB overhead. This means that messages will be kept in the database for 30 days until the deletion job removes them. Changing the retention period in the AFW (explained in Note 790226) will only affect new messages that get processed by the adapter engine. If you would like to specify a shorter period for the already processed messages because you e.g. running out of disk space a corresponding user interface is available. For details please refer to Note 807615.
d) Please note that you can explicitly maintain delete jobs in the Runtime Workbench and that the property “messaging.persistMessageRemover.checkInterval” is not used any more (only for initial calculations after installation). See Note 816022, question 20, for details.
e) Check for asynchronous messages in error (status NDLV). As stated above only message in a final state can be archived. Asynchronous messages in error can be neither deleted nor archived. Therefore messages in error have to be cancelled first. Canceling messages in the AFW is done in the RWB via “Message Monitoring”. To find the messages in error choose your adapter engine and either select the Database Overview or the Database page (select status “All Containing errors”). If you have many messages in error you can use the “Multiple Selection” button to cancel messages. You can also use the RWB -> Message Monitor -> Database overview to easily check the messages in error and perform a manual mass cancellation or restart of these messages.
Since it should be a daily task to check for messages in error not too many erroneous messages should exist in your system. A cancel job comparable to the one on the ABAP side does currently not exist for messages in the AFW.
f) The entries in table BC_MSG_AUDIT (XI_AF_MSG_AUDIT for 7.0x and lower) are the audit log entries visible in the RWB. In 7.1 and higher per default the audit log is only written to database for error messages. More information can be found in Note 1314974. Per message several lines are written to the BC_MSG_AUDIT table resulting in a much higher count of records in the audit table. The persistence of the audit log entries is directly related to the persistence of messages in BC_MSG table. Therefore follow the points above to reduce the number of messages in BC_MSG and that will also reduce the number of messages in the BC_MSG_AUDIT table.
4. Basis related areas for archiving & deletion
a) Alert data in the Alert Management (ALM)
If you use the local Alert Framework to generate alerts e.g. by using Message based alerting you also have to consider deletion (archiving) of alerts in your PI system. Based on the number of alerts generated table SALRT can grow significantly otherwise.
To delete alerts the job RSALERTPROC has to be scheduled. A detailed description can be found in the ALM section at http://help.sap.com/saphelp_nw73/helpdata/en/3f/81023cfa699508e10000000a11402f/frameset.htm
b) Application log data
Depending on the setup of your monitoring (PMI or customer developed application monitoring) also application logs can be written. Based on the number of application logs written in your system you might detect fast growth of table BALDAT. To delete these entries you can use transaction SLG2 as described in Note 195157.