Solved

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

  • 29 January 2021
  • 4 replies
  • 380 views

Userlevel 6
Badge +4

Hello, according to the below guide, I fully understand it is not allowed in some way to delete the "audit history".

https://help-2020r2.acumatica.com/Help?ScreenId=ShowWiki&pageid=5ce60e66-6997-4aa1-8496-b1bb106c41cf

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.

icon

Best answer by Gabriel Michaud 29 January 2021, 20:14

View original

4 replies

Userlevel 7
Badge +9

@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

 

Userlevel 3
Badge

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

Userlevel 4
Badge +1

@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
 

Userlevel 3
Badge

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.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2022  Acumatica, Inc. All rights reserved