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.
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.
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…