Upgrading from 2017 to 2019 - Timeout

Question

During the upgrade process, there is a timeout error: 
 Loading SQL Script File clup_app.sql Error - create procedure spFixActivityLog... 
 Microsoft OLE DB Driver for SQL Server Error 3633: 
 Query timeout expired 
 
 This procedure deals with the tciDBActivityLog, we have 74,027,184 rows. So I implemented my own procedure that will move rows with an ActivityDate greater than 30 days and put them into an Archive table...then delete from the archive table after 1 year of storage. 
 
 My fear however, is my tciMaintAuditLog table, 37,459,433 rows. Do any of you happen to do maintenance on this table like I have implemented on tciDBActivityLog? Is there a Best Practice on this?

Contefication · Accepted Answer

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.

Contefication · Answer

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.

Sage 500

Upgrading from 2017 to 2019 - Timeout

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING