Absent Member.. in-trouble-man Absent Member..
Absent Member..
222 views

HPOO database growing until using all available disk space

Hi,

 

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,

 

Youssouf

Labels (1)
0 Likes
7 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: HPOO database growing until using all available disk space

Hi,

 

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.

 

Regards,

 

0 Likes
Absent Member.. in-trouble-man Absent Member..
Absent Member..

Re: HPOO database growing until using all available disk space

Hi Chris

 

HPOO Version is 10.10. Is there a specific document, to your knowledge, in Product manuals for OO 10 I should check first ?

 

Regards

0 Likes
Absent Member.. in-trouble-man Absent Member..
Absent Member..

Re: HPOO database growing until using all available disk space

Database guide should give some informations

 

Regards

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: HPOO database growing until using all available disk space

HI,

 

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.


Regards,

0 Likes
Absent Member.. apresence Absent Member..
Absent Member..

Re: HPOO database growing until using all available disk space

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.

0 Likes
Absent Member.. in-trouble-man Absent Member..
Absent Member..

Re: HPOO database growing until using all available disk space

Hi

 

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

 

Regards

0 Likes
Absent Member.. apresence Absent Member..
Absent Member..

Re: HPOO database growing until using all available disk space

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.

 

  1. 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:
    http://msdn.microsoft.com/en-us/library/ms189272%28v=sql.105%29.aspx
  2. 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:
    http://technet.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx
  3. 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.
  4. 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:
    http://msdn.microsoft.com/en-us/library/ms189493%28v=sql.105%29.aspx
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.