Register Login

Preventing Basis tables from increasing considerably

Updated May 18, 2018

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.

 


×