Skip to main content
Solved

Database Optimization: Space Usage


AndrewBGL
Pro I
Forum|alt.badge.img

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.

 

Best answer by pallikasharma11

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 

 

View original
Did this topic help you find an answer to your question?

15 replies

Manikanta Dhulipudi
Captain II
Forum|alt.badge.img+13

Hi @AndrewBGL 

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

Did get a chance to review this 

https://community.acumatica.com/maintenance-and-troubleshooting-229/how-to-reduce-the-database-size-by-removing-old-file-revisions-4007?tid=4007&fid=229

 


AndrewBGL
Pro I
Forum|alt.badge.img
  • Author
  • Pro I
  • 175 replies
  • March 8, 2024

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'

 


AndrewBGL
Pro I
Forum|alt.badge.img
  • Author
  • Pro I
  • 175 replies
  • March 8, 2024
manikantad18 wrote:

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. 

 


Manikanta Dhulipudi
Captain II
Forum|alt.badge.img+13
AndrewBGL wrote:
manikantad18 wrote:

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


Forum|alt.badge.img+1
  • Acumatica Employee
  • 45 replies
  • Answer
  • March 8, 2024

AndrewBGL
Pro I
Forum|alt.badge.img
  • Author
  • Pro I
  • 175 replies
  • March 8, 2024

@manikantad18 I am reading the post now.

 


AndrewBGL
Pro I
Forum|alt.badge.img
  • Author
  • Pro I
  • 175 replies
  • March 8, 2024
pallikasharma11 wrote:

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.


AndrewBGL
Pro I
Forum|alt.badge.img
  • Author
  • Pro I
  • 175 replies
  • March 8, 2024

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


Forum|alt.badge.img+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 


eolivier39
Freshman I
Forum|alt.badge.img
  • Freshman I
  • 11 replies
  • March 8, 2024

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

 


AndrewBGL
Pro I
Forum|alt.badge.img
  • Author
  • Pro I
  • 175 replies
  • March 8, 2024
pallikasharma11 wrote:

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?


AndrewBGL
Pro I
Forum|alt.badge.img
  • Author
  • Pro I
  • 175 replies
  • March 8, 2024
eolivier39 wrote:

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.


AndrewBGL
Pro I
Forum|alt.badge.img
  • Author
  • Pro I
  • 175 replies
  • March 8, 2024
AndrewBGL wrote:
pallikasharma11 wrote:

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?

 


AndrewBGL
Pro I
Forum|alt.badge.img
  • Author
  • Pro I
  • 175 replies
  • March 12, 2024

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. 

 


AndrewBGL
Pro I
Forum|alt.badge.img
  • Author
  • Pro I
  • 175 replies
  • March 12, 2024

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. 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings