This tutorial provides an overview of administrative Basis tables that may become much bigger, and thereby cause problems, if the entries are not regularly deleted or archived, or if the configuration is incorrect.
- Significant growth in certain tables
- Performance problems due to large tables
- Performance problems due to index fragmentation
The tutorial does not deal with application tables and the options associated with them for archiving data.
You should regularly delete or archive the entries (listed in the tables below) that are no longer required. In addition to this, you must check to some extent whether the configuration is correct. You should also regularly reconstruct the table indexes on certain database systems, such as Oracle, because performance problems and unnecessary space requirements may occur due to index fragmentation if you do not.
Furthermore, you should prevent database statistics on these tables from being compiled at a non-representative time, as unfavorable access paths may be created if you do not.
Note that the size of the tables at database level remains unchanged after you delete entries on databases such as Oracle. To recover this space, you must reorganize the table.
The following Basis tables may cause problems as a result of unnecessary growth:
1. Application log tables: BALHDR, BALHDRP, BALM, BALMP, BALDAT, BALC, BAL_INDX
2. IDoc tables: EDIDS, EDIDC, EDIDOC, EDI30C, EDI40
3. Tables for linking IDocs: IDOCREL, SRRELROLES
4. Work item tables: SWFGPROLEINST, SWP_HEADER, SWP_NODEWI, SWPNODE, SWPNODELOG, SWPSTEPLOG, SWW_CONT, SWW_CONTOB, SWW_WI2OBJ, SWWCNTP0, SWWCNTPADD, SWWEI, SWWLOGHIST, SWWLOGPARA, SWWWIDEADL, SWWWIHEAD, SWWWIRET, SWZAI, SWZAIENTRY, SWZAIRET, SWWUSERWI
5. Tables with ALE change pointers: BDCP, BDCPS, BDCP2
6. Tables with change logging: DBTABLOG, DBTABPRT
7. tRFC and qRFC tables: ARFCSSTATE, ARFCSDATA, ARFCRSTATE, TRFCQDATA, TRFCQIN, TRFCQOUT or TRFCQSTATE
8. Oracle, BR*TOOLS tables: SDBAH, SDBAD, DBMSGORA
9. Buffer synchronization: DDLOG
10. Batch input: APQD
11. TemSe tables: TST01, TST03, TSPEVJOB
12. XMI interface tables: TXMILOGRAW, TSPEVDEV
13. Short dump table: SNAP
14. CRM middleware tables: SMO8FTCFG, SMO8FTSTP, SMO8_TMSG, SMO8_TMDAT, SMO8_DLIST, SMW3_BDOC, SMW3_BDOC1, SMW3_BDOC2, SMW3_BDOC4, SMW3_BDOC5, SMW3_BDOC6, SMW3_BDOC7, SMW3_BDOCQ, SMWT_TRC
15. Print parameter table: TPRI_PAR
16. BW tables: RSMONMESS, RSSELDONE: Important: Deleting entries from these or other BW tables (RS*DONE, RSMON*) is NOT permitted. As of BW 7.0, however, you can use the archiving object BWREQARCH to archive old entries from these tables.
17. Update tables: VBDATA, VBMOD, VBHDR, VBERROR
18. Change pointers for loans: VDCHGPTR, JBDCPHDR2, JBDCPPOS2
19. Workflow event trace: SWELOG, SWELTS, SWFREVTLOG
20. Table Analysis: ARDB_STAT0, ARDB_STAT1, ARDB_STAT2
21. qRFC analysis data: QRFCTRACE, QRFCLOG
22. Dictionary logs: DDPRS
23. Job tables: TBTCO, TBTCP
24. MDM feedback tables: MDMFDBEVENT, MDMFDBID, MDMFDBPR
25. BW workbook tables: RSRWBSTORE
26. Temporary BW tables: /BI0/0*
27. Job logs for Demand Planning: /SAPAPO/LISMAP, /SAPAPO/LISLOG
28. CRM-CIC logs: CCMLOG, CCMLOGD, CCMSESSION, CCMOBJLST, CCMOBJKEYS
29. XI Integration Server tables: SXMSPMAST, SXMSPMAST2, SXMSPHIST, SXMSPHIST2, SXMSPFRAWH, SXMSPFRAWD, SXMSCLUR, SXMSCLUR2, SXMSCLUP, SXMSCLUP2
30. XI ccBPM tables: SWFRXIHDR, SWFRXICNT, SWFRXIPRC
31. XI adapter framework: XI_AF_MSG, XI_AF_MSG_AUDIT
32. CRM-BDOC links: SMW0REL, SRRELROLES
33. CO information system: COIX_DATA40
34. CO extracts: T811E, T811ED, T811ED2
35. BW statistics data: RSDDSTATAGGR, RSDDSTATAGGRDEF, RSDDSTATCOND, RSDDSTATDELE, RSDDSTATDM, RSDDSTATEVDATA, RSDDSTATHEADER, RSDDSTATINFO, RSDDSTATLOGGING
36. PSA error logs: RSERRORHEAD, RSERRORLOG
37. Logs for receivable adjustments: DFKKDOUBTD_W, DFKKDOUBTD_RET_W
38. DTP error log: RSBERRORLOG
39. Data which is packed in binary form: INDX
40. SAPoffice tables and Business Workplace tables: SOOD, SOOS, SOC3, SOFFCONT1, BCST_SR, BCST_CAM
41. Internet Communication Framework: ICFRECORDER
42. CRM Interaction Center Trace: CRM_ICI_TRACES
43. PSA logs: RSPCINSTANCE
44. DBACOCKPIT Oracle histories: GVD_BGPROCESS, GVD_BUFF_POOL_ST, GVD_LATCH_MISSES, GVD_ENQUEUE_STAT, GVD_FILESTAT, GVD_INSTANCE, GVD_PGASTAT, GVD_PGA_TARGET_A, GVD_PGA_TARGET_H, GVD_SERVERLIST, GVD_SESSION_EVT, GVD_SESSION_WAIT, GVD_SESSION, GVD_PROCESS, GVD_PX_SESSION, GVD_WPTOTALINFO, GVD_ROWCACHE, GVD_SEGMENT_STAT, GVD_SESSTAT, GVD_SGACURRRESIZ, GVD_SGADYNFREE, GVD_SGA, GVD_SGARESIZEOPS, GVD_SESS_IO, GVD_SGASTAT, GVD_SGADYNCOMP, GVD_SEGSTAT, GVD_SPPARAMETER, GVD_SHAR_P_ADV, GVD_SQLAREA, GVD_SQL, GVD_SQLTEXT, GVD_SQL_WA_ACTIV, GVD_SQL_WA_HISTO, GVD_SQL_WORKAREA, GVD_SYSSTAT, GVD_SYSTEM_EVENT, GVD_DATABASE, GVD_CURR_BLKSRV, GVD_DATAGUARD_ST, GVD_DATAFILE, GVD_LOCKED_OBJEC, GVD_LOCK_ACTIVTY, GVD_DB_CACHE_ADV, GVD_LATCHHOLDER, GVD_LATCHCHILDS, GVD_LATCH, GVD_LATCHNAME, GVD_LATCH_PARENT, GVD_LIBRARYCACHE, GVD_LOCK, GVD_MANGD_STANBY, GVD_OBJECT_DEPEN, GVD_PARAMETER, GVD_LOGFILE, GVD_PARAMETER2, GVD_TEMPFILE, GVD_UNDOSTAT, GVD_WAITSTAT, ORA_SNAPSHOT
If you are using an Oracle database, you can determine the largest of these tables using the SQL command (Space_LargestTables.txt and ONLY_BASIS_TABLES = 'X').
Solution
The following solutions are available, depending on the table types listed above:
1. Delete old entries.
2. Archive old entries.
3. Regularly schedule RSRLDREL.
4. Archive entries that are no longer required.
5. Regularly schedule report RBDCPCLR to delete change pointers that are no longer required.
6. Delete or archive the entries that you no longer require. Check also whether table logging is activated unnecessarily for individual tables.
7. Check the proposals.
8. Regularly execute "brconnect -f cleanup" to remove old entries from these tables (and to delete old log files).
9. DDLOG occupies a special position because its entries are usually deleted automatically again as part of the buffer synchronization process. However, in certain problem situations, it may be that no entries are deleted and the table continues to increase in size. As a workaround, in this case, you can stop all SAP instances and empty DDLOG using "TRUNCATE TABLE DDLOG".
10. Reorganizing TemSe and spool.
11. Schedule the job RSBTCPRIDEL regularly.
12. Only in exceptional cases should the workflow event trace be temporarily activated in production systems. After an analysis of the problem, it should be deactivated again by using transaction SWELS.
13. If necessary, unnecessary entries can also be deleted in transaction TAANA.
14. You can use transaction SMQE to delete entries from QRFCTRACE and QRFCLOG that are no longer required.
- To deactivate active traces, you must delete all queues with the type "T" in SMQE. In the case of active logs, delete the queues with the type "L".
15. You can use the reports RADPROTA and RADPROTB to select and delete Dictionary logs that are no longer required.
16. Schedule the job SAP_REORG_JOBS daily, in order to delete old entries from TBTCO and TBTCP.
17. You can use the report RMDM_CLEAR_FEEDBACK to delete feedback data that has already been returned to the Master Data Server.
18. Check whether there are workbooks that are no longer required that you can delete.
19. Delete the superfluous temporary BW objects if required.
20. You can delete old data using transaction KE39.
21. You can delete BW statistical data using report RSDDK_STA_DEL_DATA/RSDDK_STA_DEL_DATA_TO_DATE (BW 3.x) or RSDDSTAT_DATA_DELETE (BW 7.x) or using transaction RSDDSTAT .
22. If the relevant requests are deleted from the PSA, the system usually automatically deletes the PSA error logs. Otherwise, you can use RSSM_ERRORLOG_CLEANUP to delete them.
23. Cleaning up table INDX.
24. The Internet Communication Framework table ICFRECORDER is filled only if recording is explicitly activated and automatically cleaned up as part of the SAP performance collector. If problems regarding growth and performance still occur in particular cases, check the following points:
Use transaction SICF to ensure that no unnecessary recordings are active.
Delete the contents of the table ICFRECORDER, if required, using database means (for example, using TRUNCATE), if you are sure that this data is no longer needed for logging or analysis purposes.
25. You can delete old entries in the table CRM_ICI_TRACES using transaction CRM_ICI_TRACE or the function module CRM_ICI_DELETE_TRACES.
26. Use the report RSPC_INSTANCE_CLEANUP to delete old data from the table RSPCINSTANCE.
27. Deactivate the DBACOCKPIT Oracle histories.