How to purge system activity records

ERP 10
#1

How do you guys purge System Activity records?

When I open the search box in System Activity Purge, there is no option to enter a cut-off date. Unfortunately, in 10 months, this table has grown to 30GB and 90M rows! I bet “return all records” would crash Epicor, or put a serious hurt on the DB.

I tried entering a date in the ‘Starting At’ searchbox - It wouldn’t take a forward slash, so I entered the date as 02162019 for Feb 16, 2019, but the search returned results after that date.

I wasn’t able to get a QuickSearch or BAQ search set up, since BAQ would time out during my testing.

KB0029334 states:
To delete old System Activity Log records:

  1. Within Epicor, from the main menu navigate to System Management > Purge/Cleanup Routines > System Activity Purge.
  2. Click the search button.
  3. Select an Ending date of one month earlier from today (or some other criteria)

image

image

#2

What table is it out of curiosity?

#3

Utah, it’s the Ice.SysActivityLog table.

#4

thank you

#5

The “Starting at” filed is the Sequence Number (i.e. record number). I just kept increasing the number by 10X, until I got no results. My last number is 47,602 (well under your 90,000,000).

They appear to be ordered oldest (lowest Seq #) to newest (highest Seq #). And setting your search to return more than 10,000 records will return them in the search window. But only the first 10,000 are returned to the form (even when I click “Select 1-30,000”)

#6

Might be a bit off topic, but what is the Ice.SysActivityLog table used for? We’ve been running Epicor for over 5 years and my last number is 48. The only entries I see are related to “Log on failure” if someone tried to enter a bad password (we use single sign on so this would only happen if someone tried to use the wrong app server).

#7

Entries only get added when Logging is enabled in Company Maintenance:

image

@askulte - I selected the frist 10 Seq numbers (1-10) and then choose “Purge All” from the Actions menu. It created 10 distinct calls to SysActivityLog.DeleteByID()

image
image716×249 8.81 KB

So just make a BPM on something that calls that method 90 million times. :wink:

#8

Thanks for clarifying. If you have access to the db and since it is only a log, why not just delete anything over a set number of days old using sql (just don’t tell epicor). To purge anything over 7 days old:

delete SysActivityLog  from ice.SysActivityLog where LastActivityOn <= dateadd(DAY,-7,getdate())

Alternately, turn off logging and delete everything from that table

#9

Calvin,
Thanks. That makes sort of sense… Although my search is ascending, so whatever I enter in the ‘Starting At’ box, I get that, and the records after it, not before it. I’m trying to purge the first 90M (hah!) records. I’ll start with the first 100.

image

image
image989×418 34 KB

@tanner - I’d like Epicor to tell me it’s OK to use SQL to delete those records. As far as I can tell, they are not used anywhere else, and it should be safe… Simple enough to delete them, though…

We’ve tracked the bulk of the entries as automated ‘Log on’ that happens every .3 seconds from our shopping cart integrations. We’ve turned off the logging until that’s resolved.

Thank you both!

#10

I was able to load 30,000 into the form, using search options. But the Purge All ended up crashing my client. Or maybe I just didn’t let it run long enough. After 10 minutes or so, I terminated the client and
restarted. It showed that only about the first 22,000 records were purged.

edit

I did a test of loading 1,000 records and selecting “Delete All”. it took 35 sec. So about 35 mS per record. My prior test of 30,000 records would have taken ~ 17m 30s. I probably just didn’t wait long enough.

#11

The client doesn’t like that many records on any screen, especially if it has to transact on them.
I would want to write the simple BPM that does this.

FYI, my little “trick” to avoid the “no SQL updates”, is to write it in a BPM and update the Db entity instead. No warranty issues there.

#12

@ckrusen - Thanks for testing!

@Jason_Woods - Great idea! But silly question - How? With widgets? Or is C# needed (I can copy and modify code, but not create from scratch yet).

I started a Method Directive Post BPM on SysActivityLog.DeleteByID, but not sure how to trigger it with a condition. I’d like to delete all records that have a SysActivityLog.LastActivityOn date older than “X” (I’m OK with hard coding a date in this BPM…). Or even delete just the millions of bogus LogOn rows from a specific SysActivityLog.UserID would get me 99% there!

image
image712×534 20.9 KB

image
image1076×541 33.7 KB

#13

I normally do the “Mass Update” code in ABCCode.GetNewABCCode.
The logic really is only fast if you use code to update the Entity instead of a BO Method.
DISCLAIMER: I am NOT recommending this, simply sharing!!!

DateTime sixMonthsAgo = DateTime.Now.AddMonths(-6);
foreach (var L in Db.SysActivityLog.Where(L => L.ActivityType == "Log in sucess" || L.LastActivityOn < sixMmonthsAgo ).ToList())
{
  Db.SysActivityLog.Delete(L);
}
#14

Do you really think that Support would be okay with DB manipulation outside of using BO’s? (which DMT and REST both use) And that there would be no warranty implications to having admitted to using your “trick” ?

#15

It’s all in how it is worded :wink:
“I did NOT do any direct SQL manipulation.”

That being said, I was assuming when E10 came out that the logic would be moved into SQL triggers. This would have removed a lot of potential for errors… Not saying it “should” have been that way, but cascading deletes made sense to me…

1 Like
#16

Epicor came through! Since it’s an audit table only, they recommended "TRUNCATE TABLE Ice.SysActivityLog ". Worked great. Got my space back in the test environments after doing a db shrink. We’ll probably leave the open space in LIVE, since it’ll eventually grow to fill it.

Doing some tests prior to the SQL script, I was able to delete 50k records in 5 minutes while logged onto the client, physically on our app server (figure it’s got massively more horsepower than my desktop client, and one less layer for networking).

Lesson of the day - If you turn on logging, check it the next day to make sure it’s in the ballpark of what you expected!

10k records 2m48s (3.5k records/min):
• Search: 1:39
• Select All & Retrieve: 0:24
• Purge All: 0:45

50k records 6m59s (7k records/min)
• Search: 2:17
• Select All & Retrieve: 0:52 (Memory at 472MB for Epicor.exe)
• Purge All: 3:50

250k records 45:55 (5.4k records/min)
• Search: 0:40 (wow, that was fast!)
• Select All & Retrieve: 23:19 (Memory up to 1.8GB, 16% CPU)
• Purge All: 20:56

500k records:
• Search : 1:27
• Select All & Retrieve: Client closed (crashed) at 2hr13 minutes (Memory up to 2.8GB, 13% CPU)
• Purge All: —
image