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

[Thomas_Reu]

Robert, from your last comment it appears you have an onbase production database on your server and a test database also on that server. However, this isn't stated directly, so I'm uncertain. If this is correct please know that this is extremely dangerous, since a query against the test db could suck up all the resources on the server and this would bring the production system to it's knees. It seems obvious, but I've actually seen it happen, so I don't assume anything.

[Robert_Mee1]

Tom,
This is not a test database. Just another database on the same server where we would store log data copied from production. It would be very rare for us to run queries against it and if we did they would not be resource intensive.

[Ansley_Ingram]

Robert,

Thanks for clarifying about the list of tables you included in the original post!

Our customers have come up with a variety of different solutions for archiving log data outside of the standard OnBase tables and hopefully some will share those with you. It is important to note that this log information will no longer be available within the software views, if you do archive it to another database and purge it. For Document History (i.e. hsi.transactionxlog), this is often not acceptible in the Healthcare industry, but it may be for you.

The hsi.wftransactionlog and hsi.wftransactionmsg tables don't typically grow terribly large, unless a significant amount of logging is enabled in your Workflow solution. The amount of logging to these tables is nearly entirely under your control in the design of your Workflow solution - choosing 'Log Execution' on Ad-Hoc Tasks, Actions, etc. triggers these to be logged. Often, the level of logging is overkill, for lack of a better term, in that someone will enable logging on the Ad-Hoc Task and then also on each and every Action below it - when logging the Task itself would've been sufficient. If you're seeing unacceptable growth in these tables, I'd recommend reviewing your Workflow solution to see if there are places you really do not need logging enabled. 

Ansley

[Kevin_Cole]

I actually came here looking for precisely that; a way to purge no-longer-needed records from hsi.wftransactionlog.

I thought that the functions for deleting log records from the database provided by the windows client would do; but this particular table is not included.  And we do have a sizable number of records that we no longer need.  In the grand scheme of things it's not a killer; 21M records.  But it would be nice to have some control over this growth.  Ours is a fairly old installation.  We've got records there from the summer of 2006.  How about adding this table to the list of logs that can be purged?

[Paul_Lloyd]

If you have the appropriate Admin privileges entries in the Workflow Transaction Logs, including hsi.wftransactionlog table entries, can be purged using one of the  Workflow Log menu entries: 

Restricted Purge includes options to allow purging of entries older than a certain date: