Solved

Database Optimization: Space Usage


Userlevel 6
Badge

Hello,

I have a test tenant that has grown to 41GB. Does anyone have any experience with optimizing a tenant size. 

I have already deleted all old snapshots. The majority of the data are in the Note and UploadFileRevision tables.

 

icon

Best answer by pallikasharma11 8 March 2024, 20:25

View original

15 replies

Userlevel 6
Badge

In total about 35 GB of data was removed and the test tenant is no 6.6 GB in size. 

Thanks for all the responses. 

Userlevel 6
Badge

FYI, I was finally able to fix this issue by adding a database script to delete the data from the notes. Here is a screen shot of the script. 

 

Userlevel 6
Badge

Hello @AndrewBGL 

Sharing this help article which goes over Cleanup of database https://help-2023r1.acumatica.com/(W(4))/Help?ScreenId=ShowWiki&pageid=fb069b91-154b-4745-8991-300d5c8a6ae3 

 

Checked the article. How would I check the notes table?

I used the Search In Files to find the files that are in the upload. I do not see a way to sort or filter by files size. Is there a way to find what files are the largest?

 

Userlevel 6
Badge

What about using a share drive for attachments (Uploaded Files )….. instead of saving to the Database ?

 

Yes that is a good idea. For large attachments like photos or videos we use our server to upload.

Userlevel 6
Badge

Hello @AndrewBGL 

Sharing this help article which goes over Cleanup of database https://help-2023r1.acumatica.com/(W(4))/Help?ScreenId=ShowWiki&pageid=fb069b91-154b-4745-8991-300d5c8a6ae3 

 

Checked the article. How would I check the notes table?

Userlevel 2
Badge

What about using a share drive for attachments (Uploaded Files )….. instead of saving to the Database ?

 

Userlevel 4
Badge +1

The Note table will most likely require a SQL script. Recommend testing it first  

delete top (10000) f
    from  Note f
    where CompanyID = 2 

Userlevel 6
Badge

Since the Note is the largest table is there a way to optimize it?

Userlevel 6
Badge

Hello @AndrewBGL 

Sharing this help article which goes over Cleanup of database https://help-2023r1.acumatica.com/(W(4))/Help?ScreenId=ShowWiki&pageid=fb069b91-154b-4745-8991-300d5c8a6ae3 

 

Thanks for sharing the help article.

Userlevel 6
Badge

@manikantad18 I am reading the post now.

 

Userlevel 4
Badge +1

Hello @AndrewBGL 

Sharing this help article which goes over Cleanup of database https://help-2023r1.acumatica.com/(W(4))/Help?ScreenId=ShowWiki&pageid=fb069b91-154b-4745-8991-300d5c8a6ae3 

 

Userlevel 7
Badge +12

Hi @AndrewBGL 

Can you delete the tables which are not needed and decrease the size?

I do not have experience with deleting tables and wanted to see what options I have. 

 

https://community.acumatica.com/maintenance%2Dand%2Dtroubleshooting%2D229/how%2Dto%2Dreduce%2Dthe%2Ddatabase%2Dsize%2Dby%2Dremoving%2Dold%2Dfile%2Drevisions%2D4007?tid=4007

Userlevel 6
Badge

Hi @AndrewBGL 

Can you delete the tables which are not needed and decrease the size?

I do not have experience with deleting tables and wanted to see what options I have. 

 

Userlevel 6
Badge

The only option I know of is to create a customization project and use a script to delete the notes:

Delete Note where GraphType='KNCFCore.KNCFLogMaint'

 

Userlevel 7
Badge +12

Hi @AndrewBGL 

Can you delete the tables which are not needed and decrease the size?

Did get a chance to review this 

 

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 — 2024  Acumatica, Inc. All rights reserved