You servers likely can't handle the queries being run against such a large data set. You shouldn't maintain this many rows in tciDBActivityLog as they don't really serve a purpose other than troubleshooting anyway. It was implemented to allow Support to identify third-party or user-affected data changes when researching a data corruption problem in native Sage tables. You apparently have a lot of unregistered interfaces or make a lot of data changes in the back-end, but either way an archive table is superfluous. You need to determine the sources of these mass changes and they can potentially be added to the interface exceptions, but you need to be careful with that many rows in any table.

The size consideration applies to tciMaintAuditLog as well. The number of rows in that table seems to indicate you have either been using Sage 500 for a very long time, or you make a lot of changes to customers, vendors, items, etc. If you aren't reporting against the data or actively using it to identify changes, then you should consider removing some or all of the data and disabling the auditing features.

Handling tables with this much data requires a review of system resources and database configuration, unless you just decide to truncate the tables and go on your merry way.

Thank you so much for the detailed explanation. I will proceed as planned, mentioned above, because the second I begin removing data that has never been requested to restore, the data restore will be requested.

We haven't had this issue but the discussion let me to review our tables.

tciDBActivityLog  100,361,472

tciMaintAuditLog  2,373,982

Minimum activity date from the dbactivity log is 11-20-2017.

Minimum sysdate in tciMaintAuditLog is 9-10-2002

Can we safely remove records older than a certain date without negatively impacting Sage's ability to troubleshoot if needed?

Thanks.

In respect to tciDBActivityLog, the first thing to understand is that there should be few, if any, rows in this table. Although it was originally added to log direct back-end changes for Support, it can be used by anyone to monitor or review changes made to data in native Sage schema. You should be reviewing the origination of those rows, what was modified and by what application or user. I've used it numerous times to eliminate or identify front-end problems created by vendors or clients, as well as tracking down users that gained access to the back-end for nefarious purposes. There is no front-end switch or method to turn this logging off or to purge rows, and they are lacking specifically because you should be researching their origin and know something of how to manage this logging.

The tciMaintAuditLog table is the repository for the auditing enabled by the user that is available for entity types of data. The settings that control this logging are available in modules like AR, AP and IM in each company in the system. The basic report is available in SM and Sage added an Explorer view into this data in one of the recent versions.

You can remove the audit log data using the standard purge utility for the module in which you're working. This will generally delete rows that are beyond the retention setting. The audit log data has many potential uses. For example, I once added a view to it for a CRM interface so users could research changes to customer accounts.

If you elect to remove rows from these tables manually, you are potentially creating a separate set of problems, and Support, your reseller or consultant could elect to close an issue without resolution because you modified back-end data, or charge you more than a pocketful of change to fix it. And yes, there are other markers to identify back-end modifications. Something to keep in mind.