Project and Portfolio Management Practitioners Forum

Database got way to big

Cris Robin
Respected Contributor.

Database got way to big



My Database keep getting bigger and bigger (400 GB today) and there are not to many user in the system.

I am using ppm 7.1.


my 10 biggest tables  are:


ITGFIBI2007        KWFL_STEP_TRANSACTIONS                         35





ITGFIBI2007        KWFL_STEP_TRANSACTIONS_N3                15

ITGFIBI2007        KWFL_STEP_TRANSACTIONS_N6                14


ITGFIBI2007        KWFL_STEP_TRANSACTIONS_N2                12


The sizes (last column) is in GB.

Is there any way to delete the unneeded records?


Please advice




Jim Esler
Acclaimed Contributor.

Re: Database got way to big

Chances are you have a workflow that is looping and continuously creating step transitions at a rapid rate. Look at the most recent entries in these tables to see what workflows and steps the entries are associated with.

Cris Robin
Respected Contributor.

Re: Database got way to big

Thanks. OK. lets say I do and I will find it.
What can I do about the size of it.
If I will fix it, I will stop the grow, but I really need to make it smaller.
What can I do about it?

Please advice
Jim Esler
Acclaimed Contributor.

Re: Database got way to big

I think the data will be released if you delete the requests with the large number of step transitions but I have not verified that. Releasing the space may be a funtion that the DBA will need to do.

Cris Robin
Respected Contributor.

Re: Database got way to big

I got 100 MB left in my database.

Is there any table that contain history transactions or any other data I can delete from the database?

Is there any model that can check for unneeded data?


In the computer I can do registry clearance or disk defragment but can I check the PPM system for anything like that?


Please help.

I am running out of space

Cris Robin
Respected Contributor.

Re: Database got way to big

I ran out of space. but the problem is that I can't really delete anything.

When I delete a request, it delete the request and say "You successfully deleted 0 request".

And the database size do not change.

I guess it delete the request but unable to delete all the conection to it.


Please Please help.

Erik Cole
Acclaimed Contributor.

Re: Database got way to big

I agree with Jim, it sounds like you may have some short step timeouts or something that is triggering transitions at an extreme rate. Maybe look at KWFL_STEP_TRANSACTIONS grouped by instance_source_id to see if the number of transitions seems abnormally high for individual requests...something like

select instance_source_id,count(*)
join KWFL_STEP_TRANSACTION_HISTORY bb on bb.step_transaction_id = aa.step_transaction_id
group by instance_source_id
order by 2 desc

If so, maybe you can delete the offending requests. This will cascade to the KWFL_STEP_* tables.

Niraj Prabhu
Outstanding Contributor.

Re: Database got way to big


About message, "You successfully deleted 0 request".

This occurs when the deletion from the application is not possible, due to the request being tied to different entity say Project. Check the references section and try again.

Also, if you want to reduce the size of current DB, identify number of older request/package that you can delete. What all modules are you currently using? You can check each module to see if you have old information that can be deleted now.

If you want to maintain the older information, clone the DB and then perform the deletion activity.
Niraj P.
Cris Robin
Respected Contributor.

Re: Database got way to big

I had some steps with a timeout that fill the database.

but I need the requests. most of them I can not delete but I do not need the history about all the time it happened.

I do not use projects or budget. Only requests.

Can I do something about it?


Can I make the database smaller without deleting the request itself?

Acclaimed Contributor.

Re: Database got way to big



we are having the very same problem for one of our customer. The implementer who did the first implementation configured an automatic step with a timeout of 2 minutes. Because of this step, the step_transaction_history table passed 80.000.000 records (80 million).


You have 3 solutions:

1. Live with it. Of course, you should change the configuration of those problematic steps. Also, if you have to create any report / portlet from those tables, you have to really be careful on the indexing for those tables.

2. Partition the tables. This is a solution that HP support agrees to support, since they are not (still!) providing a feature for archiving the requests.

3. Delete the problematic records. Let's say that for 1 request you have 9000 transactions for a specific step. in this case, you should delete all records except for the last one (this is a must), in order to keep the consistency. This is the solution that will dramatically reduce the space required by the database. NB: in order to actually clear the space in the DB server storage, the DBA should defrag / shrink the required tablespaces!




--remember to kudos people who helped solve your problem