Is there a viable archiving solution for Transaction Logs (prior to Transaction Log Purging)?

[Nick_Nichols]

Hello database community.  Looking for some advice....

We are a 24/7 shop (health care delivery), and database maintenance on our TransactionXLog is resulting in about 30-45 minutes of significant performance degradation to the point that the thick client and Unity Client are completely unresponsive to our end-users  during table maintenance for the TransactionXLog.  We are being forced to go to monthly TrXLog database maintenance in order to reduce the OnBase "outages" to an acceptable level.  Our TransactionXLog table is currently 230GB.

To that end, we are embarking on a small project to implement periodic maintenance (purging) of our OnBase Transaction Logs in an effort to reduce the table size, and therefore reduce the regular database maintenance impact to end-users.  The act of Transaction Log Purging is fairly straightforward and simple:

However, our largest internal customer (Medical Records - Health Information Management) is indicating that certain actions contained in the transaction log (e.g., document modifications, print, e-mail, etc...) are considered to be part of the electronic medical record, and therefore needs to be kept according to our Records Management Policy.

Sooooooooo,  we plan to develop an ETL process (run quarterly ahead of the quarterly transaction log purge) and load the extracted database table segments into a new non-OnBase database, and develop a fairly rudimentary  front-end application that will allow a very small subset of our end-user management and of course the OnBase SysAdmins the ability to retrieve transaction data (e.g., actions performed on a document, actions taken by an OnBase user, security, etc...) as needed for future audits, regulatory requirements, discovery & litigation, etc...

I found an old thread from 2016 that posed some of my same questions, but no one really responded with guidance on implementing an archive for the TransactionXLog and related tables: https://community.hyland.com/en/forum/threads/53060-database-table-archiving

I also found a very good but dated (2011) write-up about Transaction Log Purging recommendations in SQL Server (maybe from Ansley?) that may still be applicable today in OnBase 18: https://community.hyland.com/blog/posts/52711-improving-performance-when-purging-the-onbase-transact...

My question to the group is: Has anyone actually implemented an archiving solution for your Transaction Logs before purging such that the transaction log data is available for querying by SysAdmins and/or selected end-users?  If so, I'd like to learn more about your solution.  We are running OnBase 18.0.1.39 on SQL Server 2012 R2 in a five-node Always-On configuration.

[Jay_Wegener]

Nick,

Thank you for using Community to ask your questions.

Let me first start by saying I work on the Database Support Team here at Hyland...

If you are trying to purge by logdate and do not have the Index from the purging thread, I would contact your first line of support. This is something we get asked for often and will gladly assist with.

Also we have had a few customers ask/implement something similar for your archiving question. Again contact your first line of support, they can assist or reach out the Database Support Team for a recommendation.

[Nick_Nichols]

Thanks for the response Jason.  I had already read the purging thread you linked above.  It is indeed good information.  I do find it interesting that a clustered index is recommended, because we have been told emphatically by the Hyland Database support team in the past that clustered indexes are a no-no, and are not supported by Hyland.  I will be reaching out to Tech Support in the near future to obtain guidance regarding the transactionxlog purge and archiving recommendations.

Just hoping that some Hyland customers have actually designed and implemented an archiving solution for the transaction logs, and would be willing to share information about it.

[Jonathan_Zuleta]

When I was a client, I remember an implementation with a custom index which improved performance and consisted in adding a logdate index for the hsi.transactionxlog table. This improved the performance of some activities with respect to the transactionxlog table, such as purging according to the log date, according to your company procedures.

You can find additional information about this in the following article on the community site:

https://www.onbase.com/community/technical_communities/databases/b/weblog3/posts/improving-performan...

[Eric_Maerz]

Hello @Nick Nichols 

Did you end up implementing an archiving solution for the transaction log and are you willing to share some details? We are also having about 160GB of Logs and are looking for a way to cut off older entries and archive them (as a document) within OnBase.

Thank you and Best Regards

Eric