Skip to Content
Author's profile photo Arun Varadarajan

Housekeeping for BW Databases

Sometime ago we had our BASIS lead call us and tell us that the disk space allocated for SAP BW was going to run out and we had to get new storage / disk.

Unfortunately this seemed to require getting additional hardware and at current usage levels – looked as if we would run out before the H/W came in.

After a scary meeting – we decided to run some analysis on the database to find out what was eating up this data …

We then ordered the tables in descending order in terms of size and found out many things which we could be doing on a regular basis..

In many cases ( We were on BI 7 SP13 then – we were deleting PSA using the process chains but then the data in the table never got deleted!!! )
We then deleted the data using SE14 …
Another thing we noticed was that SE14 in dialog used to clear the tables but not in background …
After clearing the PSA we got about 1 TB of space and gave us a lot of time to plan on increasing the disk space.

Also SE14 does not give you the luxury of choosing which requests to delete – so be careful when clearing your PSA in this manner.

Changelogs are useful for any possibility of recovering loads especially for the high volume LO extractors .. but then having changelogs that are older than 1 year usually is strange except mandated by IT Policy…

We cleared changelogs older than 1 month leading to a lot of space being saved… Here again the decision to clear your change logs should be taken on a case by case basis and only in cases where you know you can get the data back easily should you have a smaller window of maintaining the change log.

Archiving provides a very useful way of moving data from disk … and wherever people follow the three tier landscape – it makes good sense to archive older data from the DSO into either NLS or file.

You can choose to dump the data to file or use the ADK to move the data to an archive file.

once this is done – the archive files can be moved to a separate tape.

File system
In the file system – if you are using flat files heavily then it makes good sense to move older files to an archive directory and have only files which are recent in your file system.

Request data archival
Archive your request information using the archiving tool provided and your request tables especially RSMONMESS etc should become smaller and the time taken for your monitor screen should be improved… but then test this thoroughly before doing the archiving in production.

If you are not using stats actively – have selective deletions on your stats cubes to reduce the data being loaded or archive data from your stats cubes periodically if you still need to have this information.


Have periodic reorgs performed on the database if possible. In some cases it might not be feasible to do this activity every week or so due to size limitations etc but then having a planned activity for the same helps keep your tablespaces in order.

Database statistics

Run your Statistics in BW and if possible also on the database level regularly – this could be equated to checking your tire pressure or like doing an oil change – it helps in the overall performance of the car/SAP BW.

 All this is not going to be achieved in a day but then helps in keeping data sizes in control.

Most of the above were what I had seen but then some of it might have been talked about in earlier posts / blogs too…

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Marc Bernard
      Marc Bernard
      Hi Arun,

      thanks for the nice list. You are right. If you don't pay attention, your database will grow quickly.

      SAP listens and we have included the database table size of BW objects in the new BW 7.3 technical content. This will make monitoring much easier for everyone.

      A couple items to include in your list:
      - Nearline storage, which can help to reduce storage costs
      - SAP Information Lifecycle Management, which can help to automate archiving/deletion processes

      SAP Technology RIG

      Author's profile photo Bala Prabahar
      Bala Prabahar

      Thanks Arun for starting this discussion. I agree with everything you listed. I would add a few more:Bala

      Author's profile photo Yogesh Patil
      Yogesh Patil
      Hi Arun,

      It is really good compilation and very very useful. After implementation, this list should be handy for application maintainance guys to avoid blunders.

      Good to hear that SAP is going to provide such functionality in 7.3.

      Great work..!

      Thanks and Regards,