Shrink TEO Process DB

[briaho]

Hi,

Is there a best practice on how to shrink TEO Process DB? or perhaps purge old data?

Thanks,

Brian                  

[Michael Whiteley]

Brian,

This is a post on the subject from Shaun Roberts a while back. It deals with purging old data

You should make sure that most of your processes are Non-persistent or not-archiving. (under process options, make sure archiving is unchecked). Also know that if it is already unchecked, it is not being saved to the DB so you would not see things show up in operations either way.


You can do a mass deletion of that stuff from the DB and basically get a “refreshed” system.
As a first step(if you can):
Go to the PO Master UI and click File->Server Properties. Then click “Suspend Automation” and click OK.

Then follow these instructions to clean things up.

•1) Stop PO service
•2) Login to your SQL DB and go to SQL Studio
•3) You will need to run the SQL script (if you do not want to delete the completed/failed process instances, then do not delete the *Archive tables and assuming your Process DB is called TEOProcess)
Use TEOProcess;Delete from dbo.PagedOutput;Delete from dbo.ActivityInstance;Delete from dbo.WorkflowInstance;Delete from dbo.ProcessInstance;Delete from dbo.VariableInstance;
Delete from dbo.PageOutputArchive;Delete from dbo.ActivityInstanceArchive;Delete from dbo.WorkflowInstanceArchive;Delete from dbo.ProcessInstanceArchive;
•4) Restart PO Service
•5) Open master UI, disable the processes you do not want to run
•6) If you were able to Suspend automation, go back in and uncheck that and you should be done.

If anyone has any further comments, please post

Sent from Cisco Technical Support iPad App

[Shaun Roberts]

Honestly the above is what is already done by the TEOProcess grooming. So you should not have to do the above nor should you attempt to unless you are really skilled with automation and MS SQL. The above will not *actually* shrink the DB file, it will just bring down the DB size inside of the DB file.

In the DB you have two sizes..

1) Size of the DB file on the hard drive, say 10 GB. That is how much space you are taking up on the hard drive whether the DB is 99% full or 10% full

2) Size of the DB inside of the file. This is how much space inside of that alotted(say 10 GB) you are using. again could be 1% or 50%, the size of the DB *will not change* the size of what is on the disk. It will only change that when the DB inside the DB file gets too big for the DB file size, and then it will automatically expand the DB file size.

If you have set your grooming settings under Administration->Database Settings for each DB it should help keep things down. The only time we use the above is when grooming is failing for some reason or for some deep tech support routine. It is *not* a best practice or intended for an every day user type.

If your DB file is getting quite large due to the Transaction Log file then look up the SQL command DBCCShrinkfile in MS SQL. If you have lots of "stuff" going on in the DB this file can get quite large in a hurry. Here is the MSDN article on it and I use some of the examples to help me shrink T-Logs in the past, particually I use example "B".

http://technet.microsoft.com/en-us/library/ms189493.aspx

All in all, the actual size of your TEOProcess DB itself (not the T-Log) should not get that big if grooming is working as intended. 10-20 GB is ok and about as big as I've seen. Now, T-Logs I've seen grow to 50+ GB if you are not careful and you are archiving a ton of stuff to the DB, etc.

I would suggest you check the T-Log stuff from microsoft out if you have a T-Log issue, otherwise standard grooming (assuming settings are set) should be fine. If they are not and the DB is growing at a wierd rate, I suggest you contact the TAC.

-shaun

[briaho]

Mike,

Thanks for the great info. Appreciate your help!

Regards,

Brian

[briaho]

Shaun,

This is helpful, I will check out the MSDN myself, thanks for the great info.

Regards,

Brian