SAP database growth control: technical cleanup

This blog will explain about technical cleanup to reduce the SAP database growth and to regain control of it.

Questions that will be answered are:

• How to run the standard SAP clean up jobs?
• Where can I find full list of items that could be cleaned up?
• How to run the cleanup of some common objects?
• Database reorganization after cleanup?
• How can I clean up old idocs?
• How can I clean up old table logging?
• How can I clean up old application logs?
• How can I clean up old RFC logs?
• How can I clean up old change pointers?
• How can I delete workflow logging?
• How can I archive workflows?
• How can I delete SAP office documents?
• How can I delete old audit log data?
• How can I execute specific clean up for BI systems?
• How can I execute specific clean up for solution manager system?
• Many more…. use search for table name

This blog assumes you have followed the step in the blog to get insight into your fast growing SAP tables.

If you run ECC on HANA or S4SHANA check out this blog on data aging.

This blog focuses on technical data objects archiving and clean up by performing deletion. If you want to setup functional archiving, start reading this blog.

List of technical clean up items

A full list of all possible technical clean up items can be found in OSS note 2388483 – How-To: Data Management for Technical Tables. The chapters below describe the most common ones.

SAP standard clean up jobs

Using SM36 you can plan all SAP standard jobs (which include a lot of clean up jobs for spools, dumps, etc) via the button Standard Jobs.

By hitting the button Default scheduling in an initial system, or after any upgrade or support package, the system will plan its default clean up schedule.

S4HANA has different set up of standard jobs. See blog.

Clean up of old idocs

Idoc data is stored in EDI* tables. Largest tables are usually EDI40, EDIDS and EDIDC.

Old idocs can be deleted using transaction WE11.

In batch mode you can schedule it as program RSETESTD.

In the bottom of the selection screen are the technical options:

The idoc deletion job can fail if there is too many data to process. If they happens remove the 4 tick boxes here and use the separate deletion programs: RSWWWIDE, RSARFCER, SBAL_DELETE and RSRLDREL2. These 5 combined programs will delete the same, but run more efficiently. This procedure is also explained in OSS note 1574016 – Deleting idocs with WE11/ RSETESTD.

Also check these OSS notes:

• 1813141 – How to delete unnecessary workitems of IDoc processing.
• 1813166 – ALE – Deletion of IDocs with WE11 – RSETESTD
• 1947920 – IDoc archiving performance
• 2881334 – WE11 – incorrect selection
• 3024405 – WE11/RSETESTD: Runtime error when deleting table EDIQI or EDIQO
• 3140379 – Unnecessary terminations during deletion of IDocs
• 3356590 – WE11: Incomplete deletion of IDocs
• 3373625 – RSETESTD fails with SYSTEM_SHM_CHANGE_LOCK_ACTIVE runtime error

Clean up of table logging

Table logging is stored in table DBTABLOG (general information on table logging can be found in this blog). Deletion can be done using transaction SCU3 and then choosing the option Edit/Logs/Delete, or by using program RSTBPDEL.

After you apply OSS note 2535552 - SCU3: New authorization design for table logging: new transaction code SCU3_DEL will be available.

More background information: OSS note 2335014 – DBTABLOG | Reduce size. Instructions to set up periodic job: 2388295 – RSTBPDEL | Delete logs periodically.

Bug fix OSS notes:

• 2639096 – Incorrect number of deleted logs displayed upon executing the report RSTBPDEL
• 2755908 – RSTBPDEL – Performance improvement
• 3000914 – SCU3 | RSTBPDEL | Caution: The table is cross-client
• 3332738 – RSTBPDEL | Not all logs are deleted
• 3400885 – RSTBPDEL | No option to provide the start date and start time on the selection screen
• 3400905 – RSTBPDEL | No option to execute the program in test mode.
• 3479582 – DBTABLOG | What exactly happens with the execution of report RSTBPDEL

Clean up of application logging

Application logging (SLG1) is stored in tables BALDAT and BALHDR (for general information on the use of the application log, read this blog). Deletion can be done using transaction SLG2 or by using program SBAL_DELETE.

The last options to fine tune the number of logs per job and the commit counter setting do not appear by default. Select menu option Program/Expert mode first.

Read more details in the FAQ note: 3039724 – BALHDR and BALDAT: Application Log tables size increases [FAQ].

The deletion logic on expired and non-expired logs is described in OSS note 195157 – Application log: Deletion of logs.

For setting up a dynamic variant, follow the instructions in OSS note 2936391 – Dynamic variant to remove logs with SBAL_DELETE.

Tuned setting for commit counter is described in OSS note 2507213 – SBAL_DELETE runs too long.

Bug fix notes:

• 2957039 – DBSQL_TOO_MANY_OPEN_CURSOR in SBAL_DELETE
• 3037557 – SBAL_DELETE report is canceled with runtime error DBSQL_INVALID_CURSOR
• 3226151 – SBAL_DELETE: Superfluous button
• 3494926 – SBAL_DELETE / SLG2: Dump during determination of quantity

Delete old RFC data

Old RFC data can be deleted using transaction SM58, selecting some data, then in the overview screen select the menu option Log File/ Reorganize. Or by starting program RSARFCER.

More background information in OSS note 2899366 – Huge entries in table ARFCSDATA.

In this note you can also read to check SMQ1 as well, since qRFC’s are also stored in ARFCSDATA table. See blog on qRFC’s.

To delete records with update errors as well, run program RSTRFCES. See notes 3095792 – Unable to delete entries from SM58 transaction and 3245070 – How to delete tRFCs with error “Update terminated” in SM58.

Optimization and bug fix OSS notes:

• 2768164 – DBSQL_SQL_ERROR dumps on ARFCRSTATE table
• 2955836 – Delete tRFCs with a package size for report RSARFCER
• 3011600 – The throughput of report RSARFCER is getting worse
• 3171190 – Restrict the report RSARFCER to delete tRFCs in one client
• 3245070 – How to delete tRFCs with error “Update terminated” in SM58
• 3343898 – Usage of RSARFCER report
• 3468968 – RSARFCER report deletes entries for only one client

Delete old change pointers

Old change pointers occupy space in tables BDCP2 and BDCPS. You can use transaction BD22 or report RBDCPCLR/RBDCPCLR2 (3248987 – The difference between reports RBDCPCLR and RBDCPCLR2) to delete them.

Detailed description of all the options can be found in OSS note 2676539 – BD22 (Report RBDCPCLR) Options Explained.

MDG change pointers

If you are using MDG: it has its own set of change pointer tables (MDGD_CP_REP_STAT). Clean up transaction code is MDGCPDEL. Program for batch job clean up is RMDGCPCLR.

Background in OSS note 3075612 – MDG-DRF: Reducing table entry of MDGD_CP_REP_STAT.

Bug fix note:

• 3217644 – Wrong Business Object or Business System Entry in RMDGCPCLR leads to a dump

Workflows

Workflows are stored in many tables starting with SW*.

You can delete work item history with transaction SWWH or program RSWWHIDE.

This clean up will only do the work item technical history and not the workflow itself. If workflow itself can be deleted or is to be archived is a functionality decision that the depend on the business and audit needs.

The workflow deleting program can create large amount of spools. If this is not wanted use the NULL printer.

If your business is using the GOS (generic object services) to see workflows linked to a business document, and they cannot retrieve the archived work item, please follow carefully the instructions in OSS note 2356250 – Not able to view archived workflows.

Workflow archiving

Workflow archiving can be done with archiving object WORKITEM (2578826 – Archiving Object WORKITEM – tables with deletion). For archiving setup read this blog. This note explains how to run the archiving of the WORKITEM object: 2157048 – Workflow Quick Start Guide to WORKITEM Archiving. Data display for the archived work items is explained in OSS note 2748817 – How to display Workitems from archive.

Only workflows of status Completed or Logically deleted (CANCELLED) can be archived (see OSS note 2311382 – Not all work items are archived). You can use transaction SWIA for mass logical deletion (see blog, and OSS note 2650820 – Mass complete work items manually). A custom program can be used for mass cancellation in batch mode (see blog).

If you run on HANA, read OSS note 3251001 – WORKITEM tables disk size not reduced after archiving.

Bug fix OSS notes:

• 573656 – Collective note relating to Archiving in workflow
• 2672204 – Displaying archived work items with RSWWARCP
• 2805645 – SARA: Wrong count for SWWWITEXT and SWWOUTBOX
• 2805740 – SARA: Incorrect statistics for archiving object WORKITEM
• 2820016 – Solution for several issues in workflow archiving
• 2831165 – Cannot read table <TABLE> of archiving class WORKITEM
• 2855235 – Workflow Archiving: Downport of Several Corrections
• 3055193 – Workflow archiving is supported only for final flow items, but this is not reflected in archiving feature

Workflow deletion

If you want to delete the actual workflow you have to run program RSWWWIDE.

Take care that before deleting workflows you have checked that these are not needed for audit or financial proof. Some workflows will contain approval steps with a recording of who approved what at which time.

OSS notes for workflow deletion:

• 3393605 – Aged workflow data is not deleted in deletion reports
• 3421030 – Transaction SWWL with flag “delete immediately” ignores the selection and deletes all workitems
• 3479531 – RSWWWIDE cannot delete flow items of deleted workflow definitions

Orphaned workflow records

Run program RSWWWIDE_DEP to list and delete orphaned workflow records. See OSS notes 2971286 – Table SWW_PROPERTIES and 3144853 – SWWLOGHIST table size is increasing.

Large amount of documents in SAP inbox

If you have a large amount of items in your SAP inbox, you can delete them via program RSSODLIN. Background is in OSS note 63912 – SAPoffice: Delete user sessions.

Deleting SAP office documents

SAP office documents are stored in table SOFFCONT1, and can be deleted with program RSBCS_REORG. See note 966854 – Reorganization – new report. Note 988057 – Reorganization – information contains a very useful PDF document that explains what to do in cases that RSBCS_REORG is not directly can delete an SAP office document. In most cases you have to run a special program that breaks the link between the document and the data. After that is done you can delete the content. Extra explanation is in OSS note 1641830 – Reorganization of Business Communication Services data (RSBCS_REORG).

Test this first and check with the data owner that the documents are no longer needed.

For a full explanation on deleting SAP office documents (including all the pre-programs to run) and bug fix notes: read this dedicated blog on SAP office document deletion.

OSS notes:

• 3373323 – Performance and Memory issues due to ADRV and ADRVP buffering – Use Note 3404503
• 3403599 – Performance issue with accessing tables ADRVP and ADRV.
• 3503936 – RSBCS_REORG: SOC3N missing in output

Migrating SAP office documents to content server.

Usually the business will not allow deletion of SAP office document (unless they are very old). You might be ending up with a SOFFCONT1 table of 100 GB or more.

In stead of deleting SAP office documents, you can also migrate them to a content server. Read more in this blog.

Change documents

Change documents do contain business data changes to business objects. If tables CDHDR and CDPOS grow very big, you start with an age analysis. You can propose to business to delete change documents older than 10 years. 10 years is the legal time you need to keep a lot of data. Deletion is done via program RSCDOK99. If business does not want to delete, but keep the data in the archive, you can use data archiving object CHANGEDOCU. Retrieval of archived change documents is via transaction RSSCD100.

Background in OSS note: 3103201 – CD: Archivierungsmöglichkeiten für die CDPOS.

Read this extensive blog on Change document data archiving.

Bug fix OSS notes:

• 3039022 – CD: Runtime error EXSORT_FWRITE_FAILED during archiving of change documents
• 3093880 – CD: Archiving class CHANGEDOCU, creation of archive information structure, dump TSV_TNEW_PAGE_ALLOC_FAILED

Large SMIMCONT1

See OSS note 3171193 – SMIMCONT1 table size is large to run in SE37 the clean up function module CLEAN_LANG_SMIMCONT1. This only cleans obsolete entries.

SYS_LOB tables

If you have large SYS_LOB tables, most likely these are occupied with attachments. Consider setup of SAP content server (see blog) and then migrate the documents from the SAP database to the content server (see blog).

To analyze SYS_LOB tables, follow the instructions in this dedicated blog.

LTEX table

LTEX table is used for storing ALV extracts data. Use program BALVEXTR to delete old entries. See OSS note 557772 – ALV extracts: Improving the BALVEXTR management report.

Clean up old Audit log data

You can schedule program RSAUPURG or program RSAU_FILE_ADMIN with the right variant to delete old Audit log data:

Before deleting audit log data, first agree with your security officer on the retention period. More on audit log in this blog.

Clean up of user role assignment data

If you have an older system, you will find that many users will have double roles assigned, or roles with validity dates in the past. This will lead to large amount of entries in table AGR_USERS. You can clean up by compressing this data with program PRGN_COMPRESS_TIMES. Read more in this blog.

Large WBCROSSGT table

Table WBCROSSGT is used to store the ABAP where used index. Might be large after upgrade. Use program RS_DEL_WBCROSSGT to delete and program SAPRSEUB to recreate the indexes.

Clean up of web service data

If table SRT_MMASTER is growing fast, it is time for clean up of web service data: see OSS note 2231932 – ESI – How to schedule the SAP_SOAP_RUNTIME_MANAGEMENT standard background job.

Clean up of BI data

For clean up of BI data please read this dedicated blog on clean up of BI data.

In the system that BI system extracts data from, you can run diverse cleanups:

• ODQDATA: 2502498 – Huge amount of data in ODQDATA table

Clean up for solution manager system

For clean up of a solution manager system, read this dedicated blog.

Clean up for SAP Focused Run

For clean up of a SAP Focused Run system, read this dedicated blog.

Updating statistics

If you are running Oracle database it is wise to include in technical clean up job as last step the online reorganization of tables or indexes using program RSANAORA. See blog.

Clean up non-used indexes

Oracle has a function called index monitoring to check if indexes are used at all.  You can use it to delete non-used indexes. See OSS note 105047 – Support for Oracle functions in the SAP environment.