How to check the size of the database & tables

Full database and individual table sizes can be checked via DB02 transaction. In that transaction there are several functions that are useful.

Full database overview

You can have a look at the overall server size by going to the "Overview". Then select "History" and either days/weeks or months :

• Size(Gb) : Total size of the BW server storage. Includes both free and used space
• Chg.Size : Change of size of the total size between this measurement and the previous one in Go
• Free(Gb) : Size of the free available space.
• Chg.Free : Change of the free space size between this measurement and the previous one in Go.
• % Used : Percentage of total space used = (Size(Gb) - Free (Gb)) / Size (Gb)

You can calculate the used space by simply doing : Size(Gb) - Free(Gb)

Tables sizes

You can check the individual table sizes via the "Segments" section.

• In "Overview" --> Top Sizes you have the top 100 largest tables :  
  
  
  

  Note that in the Overview Top Sizes, it shows the size of the individual tables. This is not a very complete approach as many tables are actually much larger once you add indexes, logs and partitions

• Detailed Analysis : this shows the individual table sizes - Use "Detailed Analysis Aggregated" instead.
• Detailed Analysis (Aggregated) : Shows the tables size including all related objects (Index, Lobs, Partitions)

Tips on DB02 table sizes

To measure the real size of a table, it is not enough to check only the table itself. You may be underestimating the size. 

To have the full picture, you need to check all objects relative to that table :

• Indexes : most large tables have 1 or more index which can take a lot of space
• Lob Segments : In cases a lob segment is attached to the table. The lob segment is used to store the actual large data and therefore it could look like the table itself is very small... but the lob could be very large. Ex : The BW workbooks list is stored in the RSRWBSTORE table, but the actual workbooks data is stored in the attached Lob Segment
• Partitions : Large tables (and info-providers dimensions) can be split in many different partitions. You need to include all partitions.

Main system tables descriptions

Table Reorg - Updating the size of the tables after clean up

When you delete data in tables, the tables size do not always change. This depends on the configuration of the table.

Usually if a table is dropped (ie. completly deleted, not just emptied) then the change will be reflected in DB02 the next day.

But in most cases, there is no change. it is necessary to do a "Re-organisation" of the table in order to reflect the change.

The BW team cannot do this re-org so it is necessary to request it to the SAP technical team.

Before running the procedure

Delete inactive user accounts

Before launching any of the following clean up actions, it is better if all the BW users accounts in production that have not been used for a long time (<6 months - Excluding IT & non-dialog users) be deleted from the system. This is because in some cases, data cannot be deleted if the user still exists.

Take a snapshot of Database sizes before process

In order to be able after the process to verify the changes of sizes, you need to take a snapshot of the database sizes before running the process.

1 - Go to DB02

2 - Select the "Segments" and "Detailed Analysis" options

3 - Extract the list of top largest tables sizes and save it

Meta data - PRODUCTION ONLY

Delete un-used queries & workbooks

Explanation

This program deletes queries that have not been used for a long time (365days). Although this is not very large in terms of size, it allows to delete obsolete reports that no longer need to be maintained and also allows removal of all the test queries.

We do not delete all un-used queries : please check the specific page for all the exceptions.

How to

• Transaction : RSZDELETE
• Main Parameter :  Last Use Date < current date - 365
• See specific page for this process : Un-Used Query Deletion Process

Delete un-used Bex Web bookmarks

Explanation

This is to remove bookmarks that result from Web templates in SAP NetWeaver 7.0 format.

How to 

1 - Go to transaction Se38

 2 - Run program RSRD_ADMIN_BOOKMARKS_3X (For old 3.x version bookmarks)

3 - Select All users. For Created Before/Last Access, put Current Date - 365

4 - Select All bookmarks and delete

Delete Bex Broadcaster Bookmarks

Explanation

Deleting bookmark IDs and view IDs that were created by the system for information broadcasting.

Temporary Bookmark IDs / View IDs

Bookmark IDs and view IDs that have been generated temporarily for the system to generate online links. The bookmark IDs or view IDs are no longer used in broadcast settings and do not form part of a URL for any sent online links.

Bookmark IDs / View IDs Used in Sent Links

Bookmark IDs and view IDs that are no longer being used in broadcast settings but have still been sent as a part of a URL for an online link. This means that the bookmark IDs and view IDs can only be deleted when they are no longer being used in a broadcast setting.

How to

1 - Go to transaction SE38

2 - Run program RSRD_BOOKMARK_REORGANISATION

3 - Input "365" in Older Than Days

4 - Uncheck "Test Call (No Delete)" - Otherwise no real deletion will be done

Delete un-used workbooks & roles content

Explanation

This program is used to delete from the workbook storage (RSRWBSTORE) all the workbooks that are still stored but are no longer referenced in any favorites or roles. This happens for example for all the workbooks in the favorites of a user account that was deleted.

Note that the program also checks the existing workbooks that have no queries in it. This can happen for workbooks that are in fact simple documentations via excel. That is why those are not deleted (see process below).

How to

1 - Go to transaction SE38

 2 - Run program RSWB_ROLES_REORG

 3 - Choose "Only Examine BW Roles"

This report has several sections :

Delete un-used broadcast settings

1 - Go to RSRD_ADMIN

2 - Click on Settings

3 - Remove any values in Created By. 

4 - In Last Executed On, select 01.01.2000 to Current Date - 365

5 - Select the settings & delete

Statistics

Delete Temporary Tables - Optional

Explanation

 The BW System uses different temporary objects for queries and other processes.

• /BI0/01* Temporary Table

• /BI0/02* External hierarchies

• /BI0/03* Not used any longer.

• /BI0/04* Only relevant for BW3x: 04 are names of stored procedures

• /BI0/05* Names of triggers that are used while the system compresses requests

• /BI0/06* Reuse temporary
• /BI0/0D* Tables that are used by open hub functionality
• /BI0/0P* Tables used during optimized  preprocessing

How to 

1 - Go transaction Se38

2 - Run program SAP_DROP_TMPTABLES

Deleting DTP Error Log

How to 

The DTP error log can be analysed via program RSB_ANALYZE_ERRORLOG.

To delete the DTP error log :

1 - Go to transaction SE38

2 - Run program RSBM_ERRORLOG_DELETE

3 - Input "30 days" in the "but always older than ... days" parameter 

4 - Check "Delete log"

5 - Run - If large, run in Background

Deletion of BW Statistics

1 - Go to transaction SE38

2 - Run program RSDDSTAT_DATA_DELETE

3 - Select all checkboxes for data to be deleted

4 - Free Date selection : Current Date - 365

5 - In the Date up until which data is to be deleted, select "Date Selection" and then enter Current date - 365 in the "Up to Day (Incl.)" field.

Delete RSBATCH

1. Go to transaction SE38  (tcode: RSBATCH)
2. Run program RSBATCH_DIALOG and select on 'Deletion Selections'
   
3. Select 60 Days and schedule as background mode. 

Delete RSST logs

1 - Go to transaction SE38

2 - Run program RSTT_TRACE_DELETE

Clean DYNPSOURCE table

WBP

1 - Go to transaction SE38

2 - Run program RSDQ_DYNP_GP_CLEANUP

3 - Check the PDOIT

4 - Execute the program

1 - Go to transaction SE38

2 - Run program ZSAP_TEMPOREP_CLEANUP

3 - Check the PDOIT

4 - Execute the program

Delete Sent Documents - Hidden documents

1 - Go to transaction SE38

2 - Run program RSSODFRE

3 - Select Minimum Age (Days) = 60

4 - Check the 2 delete boxes

5 - Uncheck Test Mode

6 - Execute the program

Delete Sent Documents - Private Documents

1 - Go to transaction Se38

2 - Run program :  RSSO_DELETE_PRIVATE

3 - Select all types of messages (Inbox to Private Trash)

4 - For Inbox, check “All” instead of “Viewed”

5 - Uncheck Test Mode

6 - Run the program

Reorg Documents & Sent requests

1 - Go to SE38

2 - Run program RSBCS_REORG

3 - Select "Specify Age (in Days)" and select from 60 to 9999.

4 - Uncheck the "Test Mode"

Deletion of Log Files - PC Instances

1 - Go to SE38

2 - Run program RSPC_INSTANCE_CLEANUP

3 - In Older than : current date - 365

Deletion of Log FILES - Process Chain Log

1 - Go to SE38

2 - Run program RSPC_LOG_DELETE

3 - In the "Up to and including date" enter current date - 365

Deletion of Log Files - Job Status

1 - Go to SE38

2 - Run program RSBTCDEL2

3 - Run with default values and "Test Run" unchecked

Deletion of Log Files - Application logs

1 - Go to transaction SE38

2 - Run program SBAL_DELETE

3 - Select Expiry Date "and logs which can be deleted before the expiry date"

4 - In from (date/time) to (date/time) select "01.01.1900" and current date - 730 days

Delete Idocs

1 - Go to SE38

2 - Run program RSETESTD

Deletion of Change Log

How to check the biggest table of Change Log

1. Go to Tcode DB02 
   Select Space --> Additional Function --> BW Analysis. The overall size of change log will be display like picture below.
   
2. Double click on ODS & Changelog line to display detail of each DSO. Sort the size in order to see the biggest table. 
    
   
3. Then, go to check on the flow in process chain. In order to delete the changelog, it has to be deleted by process chain. 

How to Delete Change Log

1. Add object 'Deletion of Requests from process chain
   
2. Enter parameter for each DSO including in the process chain. The time should be 14 days. It can be 0 if the DSO is loaded by full
3. Run the Changelog deletion task twice.
   • One with the "Delete Activation Requests Only, no Load Requests"
     
   • One with nothing checked.
     

Note: 

• The best practice is modified process chain in development system
• Deletion of changelog should be at the end of process chain.
• Check the big table of changelog once a year and if missing process is found, modify the process chain by adding the deletion of PSA

Deletion of unused dimension entries

How to

As this program run's individually for each infocube, it cannot be launched for all. You need to identify the info-providers with the largest table dimensions and then apply the program to those cubes.

1 - Go to DB02

2 - Double click on "BW Analysis" on the left menu :

3 - Double click on the "Cubes & related objects - Dimension" line

4 - Sort the results by "Size". This provides the largest dimensions in BW. You can then run the program on those infocubes.

5 - Go to transaction RSRV

6 - Select the test "Unused entries in InfoCube dimension" and double click to add it to the tests to run

7 - Double click on the right side "Unused entries in InfoCube dimension" and enter the name of the infocube you want to clean

8 - Click on Execute

9 - Once the check is finished, go back, select the test line (on the right) and then "Correct Error" - Then select YES for Carry out Repairs.

Deletion of PSA

How to check the biggest table of PSA

1. Go to Tcode DB02 
2. Select Space --> Additional Function --> BW Analysis.  The overall size of PSA will be display like picture below.
   
3. Double click on PSA line to display detail of each datasource. Sort the size in order to see the biggest table.
   
4. To find the name of datasource. Go to SE16 - RSTSODS and enter BW Object from DB02 into Technical ODS Name. Execute it, the name of datasource will be display
    
5. Then, go to check on the flow in process chain. In order to delete the PSA, it has to be deleted by process chain. 

How to Delete PSA

1. Add object 'Deletion of Requests from PSA
   
2. Enter parameter for each data source
   The time should be:
      - Delta loading is 14 days
      - Full load is 14 days (normally 1 day is enough but to easier maintain to know each load is full or delta, we can delete after 14 day)
   
   Note: 
   1. The best practice is modified process chain in development system
   2. Deletion of PSA should be at the end of process chain.
   3. Check the big PSA once a year and if missing process is found, modify the process chain

Delete un-necessary ODS Secondary Indexes

Explanation