HPOO database growing until using all available disk space
The HPOO database has grown until there was no space left on disk. So, is there a way to free space used by HPOO database ?
Thanks in advance and regards,
On HPLN there are purge documents which describe how to run /setup purges depending on your OO version and database type.
Please let us know if you are having issues finding them.
Yes the newer versions of the Database guide that ship with the product do contain the information.
For instance on page 18 of the 10.10 guide it gives the path to download the correct version of the scripts/sql for the 10.10 version.
Make sure you get the version that corresponds to your version of OO.
Let us know if that answers your question.
Which database version are you using? The reason I ask is we have had issues with heavily-used OO database servers filling up a lot of space very quickly when using Microsoft SQL Server. There are some tricks we used to get that down to a more manageable size outside of the purge information provided in the manuals. Let me know if you're running SQL Server and I'll share.
This is SQL Server 2008 R2 that is used. And the issue still remains as running the provided sql procedures has not made database size to decrease
Caveat emptor: I am not a DBA. What I am describing here is the result of trial & error over many years. If you are unsure of any of these steps, please consult with a certified DBA. Your mileage may vary.
I am running OO 9.x, not 10.x. There are probably differences in the database, but the same concepts should apply. I've been running the configuration as outlined below successfully for several years now.
- Change your database recovery model to SIMPLE. In this model, only the latest information is stored in the database. I believe the default is FULL which keeps a history of all transactional changes until you backup the database, and storing that data takes up a lot of space. Microsoft article for 2008 R2 here:
- Turn on database compression. In particular, OO tables that have a lot of redundancy, take up a lot of space and thus compress well are runstep_history and property_history. The first tracks each step in every flow that is executed (each step of each run), the second stores the values of all tracked variables for each of those steps. I've found Page compression works best. In my case I get 25%+ compression on property_history and 60%+ compression on runstep_history. I don’t turn on compression with the other tables because the compression is negligible. To turn on compression for a table in SQL Server 2008 R2, right-click on the table and select Storage > Manage Compression, click Next, change the compression to Page, and so on. NOTE: This in theory can produce a performance hit due to additional CPU time needed to perform compression/decompression, but I haven't noticed any difference after turning it on. There is an article on the pros/cons of this here:
- Don’t track unnecessary input values. In OO studio, change "Record Under" from the default value of "<run history>" to "<not recorded>" for inputs that you are not interested in tracking, or only track them when you know they will change. By far the most space-consuming table I've seen is the property_history, which tracks the value of variables through each step of every flow run. That means if the same variable is passed 10 times in a flow that's executed 100 times, then that value is stored 1,000 times and not de-duplicated. You can even go a few steps further and disable "This step saves the raw output" and "This step saves its primary output" on any subflows you call if you do not need to record the output.
- Free the disk space. SQL Server marks sections of its files as free (due to purge, changing the model, turning on compression, etc) but normally does not free actual space on the disk until you ask it to. You could even have a scenario where the file takes up say 1TB but all the space within the file is marked as free! To free the space on the disk, use the DBCC SHRINKFILE command, or change the file to a smaller size at the database level (Right click on the database > Properties > Files). Info on DBCC SHRINKFILE here:
Kindly check the purge procedures available on the Marketplace: https://marketplace.microfocus.com/itom/content/oo-database-maintenance
Instructions of how to use them can be found in the following link: https://docs.microfocus.com/itom/Operations_Orchestration:2020.02/SQLMaintenanceOO
Customer Support Engineer
If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.
If you are satisfied with anyone’s response please remember to give them a KUDOS by clicking on the STAR at the bottom left of the post and show your appreciation.