The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Database got way to big

Highlighted
Cris Robin
Regular Collector

Database got way to big

Hello

 

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_TRANSACTION_HISTORY       35

ITGFIBI2007        KWFL_STEP_TRANSACTION_HISTO_N3    21

ITGFIBI2007        KWFL_TRANSITION_TRANSACTIONS         18

ITGFIBI2007        KWFL_STEP_TRANSACTION_HISTO_N2   18

ITGFIBI2007        KWFL_STEP_TRANSACTIONS_N3                15

ITGFIBI2007        KWFL_STEP_TRANSACTIONS_N6                14

ITGFIBI2007        KWFL_STEP_TRANSACTION_HISTO_N1    13

ITGFIBI2007        KWFL_STEP_TRANSACTIONS_N2                12

 

The sizes (last column) is in GB.

Is there any way to delete the unneeded records?

 

Please advice

 

Sagi

 

9 REPLIES
Jim Esler
Honored 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
Regular Collector

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
Honored 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
Regular Collector

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
Regular Collector

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
Honored 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(*)
from KWFL_STEP_TRANSACTIONS aa
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
Frequent Visitor

Re: Database got way to big

Cris,

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
Regular Collector

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?

AlexSavencu
Honored Contributor

Re: Database got way to big

Hi,

 

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!

 

cheers

alex


--remember to kudos people who helped solve your problem
//Add this to "OnDomLoad" event