Our new HR system uses SAP Business Objects for transactional reporting, as this was setup by our HR supplier we haven’t really got involved with it beyond writing a few custom reports, turns out we’ve written quite a few, so much so that the Business Objects CMS database has swollen to over 30 GBs. This, as you might have guessed, is causing a few performance problems. After a discussion with the supplier, we found out it was storing all the reports, even the ad-hoc one-off reports and we should have set a parameter to limit it.
After limiting it, the database still didn’t reduce the database size. A quick Google found someone saying that it is because it can’t handle over 1 million rows of old reports, which makes sense, running a SQL command that would delete over 30gb of data would have insane log file grow as well as massive performance problems.
The steps to resolve this, on MS-SQL, was to:
Stop the Business Objects services on the app tier
Ensure you have a backup of the database
Set the recovery model to simple if it isn’t already
Copy out all the required rows
Truncate the table
Re-insert the required rows back into the table
Check everything looks ok, deal with the fact the database is now tiny
Create another backup
Start the app tier backup