Is it safe to clean "Auidit history" and how?

@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!