ERP Database Log Maintenance & Diagnostic Scripts

Executive Summary

This work consolidates routine checks that used to be done one by one into a single scheduled diagnostic package. It reviews the batch queue, log table sizes, and recent errors, then removes log data that has passed its retention period.

Background

The batch history and database log tables in the production environment grew without a limit. As a result:

• Backup time and the load time of some forms increased steadily.
• Failed batch jobs were only noticed after users reported unprocessed data.
• The daily check required several manual steps that were easy to skip.

Solution Architecture

The solution is split into two parts with different responsibilities. The diagnostic part only reads and reports. The cleanup part deletes data, but only beyond a configured retention window and only outside business hours.

1. Check: batch status, error count, and table sizes.
2. Report: a plain-text summary sent to the system owner.
3. Cleanup: retention-bound deletion that logs the number of rows removed.

Technical Implementation

An X++ class was created as a batch job that can be scheduled from the AX client. Table size checks use system queries on SQL Server.

SELECT t.name AS table_name,
       SUM(p.rows) AS row_count
FROM sys.tables t
JOIN sys.partitions p ON p.object_id = t.object_id
WHERE p.index_id IN (0, 1)
GROUP BY t.name
ORDER BY row_count DESC;

Each deletion runs in small row batches so table locks do not hold up user transactions. The retention period is a parameter, not a value hard-coded in the script.

Results & Impact

Log table growth slowed once the scheduled cleanup was running, and batch failures now appear in the morning report. All changes were tested in a non-production environment before being scheduled in production.