AuditHistory: Contains information about the database size used for saving the audit history. To review the audit history, you use the Audit History (SM205530) form. You cannot delete the audit history, so we recommend that you turn on the field-level audit for no more than few forms.
But it is really large and obsoleted in database.
Can we delete, like more than 1 years old data? and how.
The data is really large and grows rapidly. If the data can not be cleaned, it would be time consuming if we like to back up a snapshot, it would take hours.
Page 1 / 1
@ray20 just delete it via SQL… ex:
DELETE FROM AuditHistory WHERE ChangeDate<=GETDATE()-365
If you have lots of rows this query will end up taking a lot of time; if you’re ok clearing the entire history (and you have a backup elsewhere), you can just do
TRUNCATE TABLE AuditHistory
@Gabriel Michaud I was wondering if you have a way that this type of Script can just clear data from the table associated with one Tenant. It seems from my use of it on my test setup it works across all tenants with the above language.
Thanks,
@greglang If you don’t want to delete ALL of the history, it was recommended to us by support that the history be deleted in batches to prevent a performance impact on the system. In the example below the delete would be in batches of 10,000. Also, you can specify the CompanyID in your script to delete from just one Tenant.
You could use something like this:
DECLARE @CompanyID int = 3 /* Your tenant id goes here */ DECLARE @r int = 1 DECLARE @cutoffdate date
SET @cnt = 0 SET @cutoffdate = '1/1/2021'
WHILE @r > 0 BEGIN Delete top(10000) from AuditHistory WHERE AuditHistory.changedate <= @cutoffdate AND AuditHistory.CompanyID = @CompanyID SET @r = @@ROWCOUNT; End
Laura
Thanks @lauraj46 that’s super helpful for the above example and maybe other ideas!
I was thinking I could use this logic to create a new tenant that only had a few year history (we’ve been in Acumatica for 9 years and have a lot of legacy data) to help our system run a bit quicker overall.
Are there any additional steps that need to be done after purging AuditHistory?
The AuditHistory table shows empty in the database but when I check the UI, I can still see all the records in the AUAuditValues DAC:
Nevermind: This works fine and I confused two instances I had (ran DB script in a different DB).
Is there a setting where you can remove audit history past X number of days? Since I am on a hosted instance, I do not have direct DB access and am unable to run commands directly on the database like I was able to on our last ERP.
@gpeterson I haven’t seen that ability.
@TimRodman could AUGSQL run a delete statement?
Hi @jwright and @gpeterson,
Yes, AugSQL (click here) can run DELETE statements. You want to be very careful, but in this situation it makes sense.
You can also schedule the DELETE statement to run on a schedule (once a day, once a week, etc.) so you can keep the AuditHistory table at a reasonable size.
Tim
First I’m hearing of AugSQL. I’ll have to look into it further!
