XI_AF_MSG_AUDIT table entries are not deleted

[Former Member]

Hi all.

We have PI 7.0 with SP13 and DB2 database.

The Adapter Framework XI_AF_MSG_AUDIT table is growing and growing and the Delete job in RWB is working once per day and it's status is ok.

I've read a lot of threads and SAP Notes about this problem but my problem is not solved.

I think something is not working ok because XI_AF_MSG table has 13000 entries and XI_AF_MSG_AUDIT has 6000000. It is as if XI_AF_MSG table entries were being deleted but AUDIT entries were persisting forever.

I tried executing the URL http://p0mappsapxi:52000/MessagingSystem/archiving/reorgdb.jsp

but nothing...

My persistDuration parameters in Visual Admin are set by default to 2592000000 (30 days) in milliseconds, but I also read in a thread that I should set persistDuration parameters in Visual Admin in SECONDS. Is that correct? Should I set persistDuration to seconds (modifying the value to 2592000)?

Thanks in advance.

Gari.

[Former Member · Accepted solution]

Hi,

we had a similar situation and once we did verify with our businesses that entries in this table was no longer required, we deleted these entries at the DB level. The syntax(at least for MSSQL DB) you can use to delete the entries are:

DELETE FROM SAP<SID>DB.XI_AF_MSG_AUDIT WHERE (TIME_STAMP < CONVERT(DATETIME, '2008-01-01 00:00:00.00'));

This will delete all the entries before 2008-01-01. However, you have to make sure that XI_AF_MSG_AUDIT and XI_AF_MSG tables are consistent, because they are interlinked.

Ofcourse, I would also run the DB consistency checks afterward to make sure that the database is healthy.

Hope this helps.

Rahul Kabra

SAP Labs - Palo Alto

[Former Member]

Hi Gari,

1) The persist duration is in Millseconds.

2) For each entry in the XI_AF_MSG table there will be multiple entries in the XI_AF_MSG_AUDIT table. This is normal because the audit log table holds more information pertaining to a Message that came in / went out of the Adapter Engine.

In a typical case this would be 10 to 15 times the entries in the XI_AF_MSG table. Yours is almost 400 times. I would recommend, you check if there are many messages in System Error or To Be Delivered Status.

Deleting all the messages in System Error Status would help get rid of entries in both tables.

3) Check and set the xiadapter.outbound.persistDuration.default and xiadapter.inbound.persistDuration.default parameters to a lower value (in terms of milliseconds) for ALL server nodes.

4) If you'd like to disable the audit logs then set "auditLogEnabled" to false in XI AF Core Service in Visual Admin for all the server nodes. But remember this will NOT write any audit logs for success or failed messages.

For 3) Please consult your company as how many days they need the adapter engine messages and audit logs to be kept.

For 4) same as for 3). Also consider raising an OSS with SAP before you do this.

Regards,

Balaji.M

[Former Member]

Hi all.

Thank you very much for your reply. I solved my problem some weeks ago and I'm sorry because I forgot to close the thread. I had already applied the changes you wrote in your answers. They are very necessary and very helpful when you have this problem and the size of XI_AF_MSG and XI_AF_MSG_AUDIT tables get very big.

But my problem couldn't be solved only changing the parameters and deleting records of those tables. There are some messages (ASYNC-SYNC-ASYNC bridge messages) that are not deleted by the Deleting Job configured (by default) in the Adapter Framework. This is a known bug that can be solved applying this note:

[Note 1246809 - AuditLog entries not getting deleted|https://service.sap.com/sap/support/notes/1246809]

Regards,

Gari.

[Former Member]

Any suggestion?

Thank you very much,

Gari.