CPO : How to clean tables dbo.ActivityInstance and dbo.ActivityInstance

[stephanebatais]

Hi

I'm looking of a procedure to clean the tables dbo.ActivityInstance and dbo.ActivityInstance in the database OrchestratorProcess of Cisco Process Orchestrator 3.0

Is there a parameter to be set in the CPO database in order to activate an automatic clean ? Is there a parameter to be set in the CPO ?

Regards

[Shaun Roberts · Accepted solution]

Hi!

  I think you are looking to clear out ActivityInstance and ActivitiyInstanceArchive in CPO's process DB. Did you notice they are very high in row count or ?

The way CPO works is like this...

1) Processes run and store that data in the *Instance tables (ActivityInstance, ProcessInstance, etc)

2) Once they reach a terminal state (succeeded, failed, etc) they are migrated over to the *InstanceArchive tables (ActivitiyInstanceArchive, ProcessInstanceArchive, etc)

3) The server GROOMS the process DB every night (by default at 11pm server time) and removes off based on your settings the older processes

So a few things here to look at...

1) What are your grooming settings? You can check that out under Administration->Database Settings. Right click on the process DB->properties. Click Configuration. You want to look at the "Process Instances Grooming". By default it's 15 days worth of data, but they might not be aggressive enough for you... so you can change it there. You can also hit the "Groom Now" button and force a groom to happen right then and there or wait until the server does it at night. This will delete things from the *Archive tables *ONLY* however.

2) Make sure you are not archiving a ton of custom process work. You should always review what you are and are not archiving in your system. If you need help determining/looking at this a TAC case would be a good start and they can assist or get in touch with me and I can look as well.

There could be a chance that grooming is failing for some reason, however, if that would be the case (and you could see that under Operations -> Auditing, then you would need to open a TAC case and submit logs so support can review them and see what's going on.

Lastly, if you are just looking to delete all the rows via SQL in those tables, and understand the possible issues that come with that, you (again) would need to open a TAC case and they could provide you with the SQL queries to do such. The preference would be to not post that stuff out here, as it's meant to be a Cisco support activity and not something we want folks doing all the time. It's really a last resort type thing and normally means you have a bigger issue going on that TAC needs to take a look at.

--Shaun Roberts

Technical Leader, Services

shaurobe@cisco.com

[stephanebatais]

In attachment the grooming settings. Firstly, I will test the "groom now" feature, and if the clean is not enough, I will review these settings.

regards

[Shaun Roberts]

ok.. also you can do a

select count(*) from dbo.ActivityInstance

select count(*) from dbo.ActivityInstanceArchive

and report back those numbers would give me a good idea if you might be having an issue or maybe just need to look at the overall picture and make some tweaks. Again this could be through less archiving, using less days back, or even using something like my Content Logger.

[stephanebatais]

I have opened the case 681184449.

Thanks for your replies.

[stephanebatais]

To complete my previous reply, the size of the tables dbo.ActivityInstance (11Gbytes / 4055 rows) and dbo.ActivityInstanceArchive (25 Gbytes / 37110 rows) is very huge !!!

I have launched the 'Groom now' feature, and the size of the two tables has decreased a bit, but enough.

I will contact the TAC ...

Regards

[Shaun Roberts]

That size to row count is odd. I have had activityinstance tables much larger in row count, but not near the same size in database size.

There are a couple of "sizes" to be aware of.

The size of the DB itself on the hard drive. (say 100 GB) And then the size the database is taking inside of that DB file. (let's say 10 GB).

Grooming will *only* reduce the size inside the DB file, not the DB file itself. The process DB also grows at 10% autogrowth, so once the space is taken, it's there for the DB. (sure you can sure some shrinkfile stuff, but I would not advocate for it).

In your case, feel free to reference this discussion and I can plug in with the TAC and we can look at your environment and see why you have these larger tables. A good sized CPO setup that is active can run decent sized DBs.

I personally run a very active 4 noded cluster for TAC that is probably 140-150GB in size for the process DB.