I’ve heard warnings many times before about how Change Logs can hinder database performance. This led me to check on the size of our Change Log table. One can do this with the following SQL query:
sp_spaceused ‘Ice.ChgLog’
It occurred to me this morning that nearly all use cases for how we use Change Logs is for something recent, for example we want to know who changed a date on an open Sales Order and have a daily BAQ report that tells us. Chances are though, once 6 months have passed we’ll never reference that change log ever again. And by the time 3 years have passed, we won’t care about ANY of the change logs that are that old.
So I am just wondering, has anyone else gone about pruning their change logs? Is it worth considering or is this a non-issue?
I haven’t tried it to confirm, but ChatGPT says that this SQL query would work for deleting all change log records order than 3 years:
DELETE FROM Ice.ChgLog WHERE DateStampedOn > DATEADD(year, -3, GETDATE());
EDIT to correct query (mistake was in my prompt to ChatGPT):
DELETE FROM Ice.ChgLog WHERE DateStampedOn < DATEADD(year, -3, GETDATE());
There is a program called Database Purge and Summarize under System Management > Purge/Cleanup Routines that has an option for Change Log. I haven’t used this and I would STRONGLY suggest doing this in a test database first, but it looks like this would do what you want: enter a cut-off date and select Change Log, and it will purge anything older than that date.
For what it’s worth…you would want to reverse that inequality in the SQL statement: DateStampedOn < DATEADD(year, -3, GetDate())
Just a word of warning… I ran this on our test database and it ended up using all of the available memory on our AppServer - to the point where some Live Epicor services (on the same App Server) were failing to start. You probably want to test this out on a separate server or after hours if possible.
