cancel
Showing results for 
Search instead for 
Did you mean: 

Delete messages in different states as table size keeps growing

Former Member
0 Kudos
641
Hi,
We have scheduled a weekly job SAP_BC_XMB_DELETE_711 with 2 steps: 1.RSXMB_DELETE_MESSAGES and 2.RSXMB_TABLE_SWITCH. This was scheduled twice a week. Few interfaces have been marked for archive. Due to growing issues it was scheduled 4 times in a week, i.e. Tuesday, Thursday, Saturday and Sunday. After this we have found that the job took nearly 24 hrs to complete and now it takes more than 32 hrs.
A huge no. of messages are just being moved everytime,
22.12.2012 01:00:14 Job started
22.12.2012 01:00:14 Step 001 started (program RSXMB_DELETE_MESSAGES, variant &0000000000015, user ID BASIS)
22.12.2012 01:04:47 35,655 XML messages deleted
22.12.2012 01:04:47 Step 002 started (program RSXMB_TABLE_SWITCH, variant &0000000000015, user ID BASIS)
22.12.2012 01:09:57 Messages Moved Until Now: 4,100
22.12.2012 06:19:58 Messages Moved Until Now: 200,244
22.12.2012 16:44:58 Messages Moved Until Now: 656,644
22.12.2012 20:24:56 Messages Moved Until Now: 800,944
23.12.2012 01:59:56 Messages Moved Until Now: 1,002,087
23.12.2012 08:34:58 Messages Moved Until Now: 1,273,287
23.12.2012 08:38:34 Persistence tables reorganized
23.12.2012 08:38:43 @5C@ Recommendation for persistence tables
23.12.2012 08:38:43 Reduce the retention period for XML messages
23.12.2012 08:38:43 Job finished
It was observed that several messages are not in final state. What can be done to delete these message from Integration Engine and Adapter Engine as well to resolve this problem.
Following are some statistics:
    

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Priyanka,

If you are mvoing huge number of messages to the copy table then there should be problem with the DROP_MAX_TABLE_LOAD parameter.

Pls. see SAP Note 872338 Point i. I copied the text below.

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'.

Example:

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.

Former Member
0 Kudos

Hi Sumit,

The current value for DROP_MAX_TABLE_LOAD is 7. Shall re-calculate. There are a certain set of messages which are marked for deletion and just being moved during every table switch. approx. 8,80,000 messages. It is because of this set that the table switch procedure takes longer to complete.

Former Member
0 Kudos

Pls. check SAP Note 872338. it has very detailed information on how to do cleanup.

Also check the adapter status of messages. Run report RSXMB_SHOW_STATUS.

Value of 7 doesnt seem to be correct. You would need to adjust.Check my earlier comment on how to calculate the new value.

Regards,

Sumit Khetawat

Former Member
0 Kudos

Hi Sumit,

Is this the correct note:

Note 872338 - Biller Direct: Reading
archived clearing documents
  ?

The parameter was reset by the basis team to 7.

The report shows as follows:

24.12.2012                                                                        Program RSXMB_SHOW_STATUS                                                                               1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
24.12.2012 15:59:37
Table Container:                                   SXMSPMAST

Overview
========
Time Stamp Not Included

Number of Messages in Database:                                           1,286,611
Number of Messages in Client:                                             1,286,611
Number of Messages for Reorganization in Client:                          1,286,611
Number of Messages to Be Archived in Client:                                450,417
Number of Logically Deleted Messages in Client:                                   0
Number of Archived and Logically Deleted Messages in Client:                      0


Message Status
==============
Message Status:      001 Number:              1,376
Message Status:      002 Number:                  0
Message Status:      003 Number:            484,644
Message Status:      004 Number:                  0
Message Status:      005 Number:                  0
Message Status:      006 Number:                  0
Message Status:      007 Number:                  0
Message Status:      008 Number:                  0
Message Status:      009 Number:                  7
Message Status:      010 Number:              1,772
Message Status:      011 Number:                247
Message Status:      012 Number:              1,014
Message Status:      013 Number:                  0
Message Status:      014 Number:            743,367
Message Status:      015 Number:                  0
Message Status:      016 Number:                  0
Message Status:      017 Number:                  0
Message Status:      018 Number:                  4
Message Status:      019 Number:                  0
Message Status:      020 Number:                  0
Message Status:      021 Number:                  2
Message Status:      022 Number:                  0
Message Status:      023 Number:              4,976
Message Status:      024 Number:                  0
Message Status:      025 Number:                  0
Message Status:      026 Number:                  0
Message Status:      027 Number:                  0
Message Status:      028 Number:                  0
Message Status:      029 Number:             49,202
Message Status:      030 Number:                  0
Message Status:      030 Number:                  0

Adapter Status
==============

Adapter Status of Messages with Message Status:              003
Adapter Status:      001 Number:             20,009
Adapter Status:      002 Number:                  0
Adapter Status:      003 Number:                  0
Adapter Status:      004 Number:                  0
Adapter Status:      005 Number:                  0
Adapter Status:      006 Number:              4,212
Adapter Status:      007 Number:                247
Adapter Status:      008 Number:                 13
Adapter Status:      009 Number:                  0
Adapter Status:      010 Number:                  0
Adapter Status >     010 Number:                  0

rajasekhar_reddy14
Active Contributor
0 Kudos

Hi Priyanka,

You have to make sure that all messages are avaiable to archived after certain time, the message which has failure status not possible to archive.If Message failed in Moni not cacelled then it causes issue, so cancel the message or reprocess the failed message.

1)Your basis team has to play key role to resolve this issue,excuting Table switch job right idea but sometimes it causes issue while mobing messages to other table, for timeving deactive this job and enable table space extended.

2)Make sure that all failed messages(IE/AE) shoube be processed or cacelled manually.

3)Your basis team can execute DB commens at OS level to identify the number of message not archived and why table spaces getting full.

4)If your DB size is small and volume high then req infra team to extend the space and use riight rentention periods to archive/delete messages.

5)I personally dont recommend to delete the messages from table manually,like exueting DB command at OS level.

it looks your Archive/Deletion jobs not working as expected, take help from basis and design clear strategy.

refer below blog series and check your system process against it

http://scn.sap.com/community/pi-and-soa-middleware/blog/2010/04/22/archiving-deletion-of-messages-in...

Regards,

Raj

Former Member
0 Kudos

Hi Raja,

The problem is that there are many messages in error and other states as you have seen which are dated two years back. We prefer cancelling then instead of re-processing as it would be outdated data. Thus the clean up required before we schedule new archiving and deletion jobs.