skip to main content

SAP NetWeaver Newbie

Switch Deletion Procedure in SAP XI


In a normal deletion procedure, when a message is eligible for deletion, it is deleted from the table with SQL delete statements. There will be a lot of deletion statements running on the table which also has inserts going on as new messages are processed. In order to make deletion faster, switch deletion was introduced. This procedure means that 2 set of tables are used in XI. At any time one set is used for storing the XI messages. When message pass the retention time, they are marked as logically deleted, but not really deleted from the table yet. As the messages keep accumulating, the fill level of the persistence layer crosses the threshold defined by DROP_MAX_TABLE_LOAD. When this threshold is crossed, the inactive set is activated and all the messages that are within retention time are copied into the active set of tables. The messages marked as logically deleted remain in the old set of tables. These tables are then truncated. This way, the messages are deleted in a single shot.

Now the problematic aspect of this procedure lies with the copying of messages that are still within retention period. Let's take an example scenario to explain this problem.

Let us say that the XI system processed approximately 250,000 messages every day. Let's assume that DROP_MAX_TABLE_LOAD is set to 90% (765,000 messages), the retetion period for messages is 3 days and there are no old messages in the system. Here is how it would look when you run the delete job every day at the end of the day.

Day 1:
250,000 messages processed. 250,000 messages within retention period.
Day 2:
250,000 messages processed. 500,000 messages within retention period.
Day 3:
250,000 messages processed. 750,000 messages within retention period.
Day 4:
250,000 messages processed. 750,000 messages within retention period. 250,000 messages logically deleted.

We now have 1,000,000 messages (750K within retention and 250K eligible for deletion) in XI. As 765,000 messages mark is crossed, the switch to new set of tables happens. 750K messages are copied to the new tables and 250K old messages remain in the old tables. The old tables are truncated, deleting the 250K old messages.

Day 5:
250,000 messages processed. 750,000 messages within retention period. 250,000 messages logically deleted.
Again 750K messages are copied to the new tables and 250K old messages remain in the old tables, which are truncated.

From Day 4 onward, the XI system copies 750K messages, which means a big number of messages are copied every day to delete 250K messages.

To prevent this problem, the retention time should be reduced or give up using switch deletion procedure and stick to simple deletion prcedure. You can increase DROP_MAX_TABLE_LOAD so that the swich happens approximately once in say 7 days, but then you are maintaining a high number of messages in XI, which again has performance implications.

Switch deletion procedure works great when 100K messages are processed for the same retention and DROP_MAX_TABLE_LOAD. Therefore work out message flow and retetion times before you choose to use switch deletion procedure.

3 comments:

  1. Hi,
    Your article is good. I have a problem with this procedure. Please help.
    By mistakenly i have activated Switch Procedure. I have kept threshold value as 20%. Now the Table size has crossed the threshold value and switch procedure got activated and started to copy all the data's within the retention period to container 2. I cancelled the switch job, since I am running out of Database space.
    What is the best way to get back to normal delete procedure.
    Currently my fill level is nearly 770%. SAP Suggesting to complete the switch job, but space..???

    Please suggest.
    Please suggest.

    ReplyDelete
    Replies
    1. Hi Sudhan
      You may not have much options left except for adding more space. Check if you can use the logical deletion report attached to SAP note 1568401.

      Delete
    2. Here is how we dealt with this issue:
      1. Run RSXMB_SWITCH_DEL_OLD_ENTRIES report to physically delete the entries that would have been copied over in the container. "Mark as logically deleted" should not be selected.
      2. Repeat this step for both active and inactive containers
      3. See if the number of entries in SXMSPMAST or SXMSMAST2 are decreasing, otherwise you did not select the appropriate options in the variant.
      4. In SXMB_ADM > Integration Engine Configuration > Specific Configuration, set DELETION - TABLE_SWITCH - MOVE_INSTEAD_OF_COPY to X
      5. Make sure that the parameter DELETION - DROP_MAX_TABLE_LOAD is set to a low value so that switch happens each time the switch job runs.
      6. Run the switch job RSXMB_TABLE_SWITCH. Run it twice so that both containers are reorganized, releasing space back to database.

      Delete

Email Subscription

Get every new post into your inbox by subscribing us.

Want a reason to subscribe?
1. This sitemap might convince you to subscribe.
2. We do not misuse email IDs. We respect privacy.

© 2008 - 2017 sapnwnewbie. All rights reserved.