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.
