Database Table Archiving

[Robert_Mee1]

We have a number of tables in our database that have had significant growth.  Some to the point that they are effecting the Database teams ability to run the sampling process. Below are some tables in question. Can anyone share how they are handling archiving?  We do not want to purge this data without archiving to another location outside of the production database first. 

wftransactionlog

wftransactionmsg

transactionxlog

itemdatapage

itemdata

dfcytxlog

wflog

keyitem584

keyitem299

keyitem306

keyitem301

keyitem300

keyitem305

keyxitem125

chartxitemdata

securitylog

keyitem588

keyitem583

keyitem585

[Ansley_Ingram · Accepted solution]

Hi Robert,

I would recommend reaching out to your First Line of Support for assistance with this question.

Purging data directly from the OnBase database is not permitted under the maintenance agreement and would be significantly detrimental given some of the tables you have listed. 

Your First Line of Support can also assist with recommendations on maintenance jobs on these larger tables to allow your maintenance plans to complete.

Ansley

[Robert_Mee1]

Ansley, thank you for your response. We did initially open a ticket with Hyland Support. They indicated “Hyland can’t really offer a best practice our system in particular. This varies based on the internal policies of our organization.” What we are looking for from the Database Technical Community is how other clients are handling their Archiving. We are hoping to get some input from others who have developed a successful strategy.

[christian_jone1]

Hi Robert,

Piggy backing off of Ansley and in response to your question in response to his answer, "how other clients are handling their Archiving[?]"

I don't know what industry your're in, but generally speaking you probably or should have an idea as to what industry you are in, what federal, state, local,  laws/policies/regulations, i.e. HIPAA, PCI compliance apply to your industry/business and would form Archiving strategies based on this information. Each customer environment is a little different and some industries, laws/policies/regulations are changing from state to state, year to year, and so on etc. Generally speaking, you may set up document retention policies in OnBase to automatically delete documents after X amount of time (years, months, etc.) Purging documents within the OnBase user interface after they have been deleted and reside in Document Maintenance will permanently remove those documents from OnBase forever. The only way to get those documents back into OnBase would be to import the documents again. Or if you have the documents backed up on a tape and/or hard drive somewhere... you would still have to import them into OnBase or manually delete them.

But no, you can't just from the database level purge out data from a group of tables for OnBase Production environment, you would void your warranty with Hyland.

I'd recommend reviewing the OnBase database reference guide:
https://www.onbase.com/community/technical_communities/databases/m/reference_guides/15762

Here the full list of OnBase database reference guides, especially the Database use policy that tells you about not voiding the warranty:
https://www.onbase.com/community/technical_communities/databases/m/reference_guides

Otherwise, if you could be more specific as to your Archive questions... perhaps the Records Management module would assist in what you want and then I'd go to your FLOS on that.

If I didn't say it earlier, each OnBase customer environment is different and based on business practice/industry is goverened by any number of different establishments, whose responsibility would fall to the customer level to design/determine/approve the indiividal archiving policies to govern a customer's OnBase environment.

I hope this helps.

[Alex_French]

Robert,

You might start by understanding what's in those tables (Database Reference Guide will help get you started, Christian linked to it), and why they're getting large in your environment specifically.

Transactionxlog is going to get large in any active OnBase environment.

Lots of tables in your list are Keyword data- are they for deleted/purged docs?

Itemdata and itemdatapage are basic information for every document in the system- are they for deleted/purged docs?

Several logs relate to workflow.  Do you have a huge workflow volume?  Are you doing more logging on individual workflow actions than you need?  Have you built workflows that do an unreasonable amount of transitioning between queues, maybe very active "subroutine" lifecycles?

If the problem motivating the question is basic database performance and calculating statistics, is your database environment configured reasonably and specced reasonably?  Hyland has a document about database setup (don't have a link off the top of my head, I'm sure Ansley would).

If your environment is processing a large number of documents and doing a lot of workflow, is your database hardware reasonable?  Are your DBAs asking a question that could be answered with less man-time and more money spent on hardware because your system has grown reasonably over time?

There are lots of people here who are better equipped than me to discuss specifics, but those are the questions I would start thinking about *before* actually changing anything in your OnBase configuration or even thinking about what it would take to archive data, maintain integrity, and stay in line with Hyland's EULA.