Database Maintenance: General Information

In MYOB Acumatica, the data for all tenants and snapshots is stored in the database and may take a lot of space. To prevent uncontrolled growth of the database (for example, if you have created many snapshots), the database size available for your MYOB Acumatica instance is limited in your license.

Learning Objectives

In this chapter, you will learn how to do the following:

  • Monitor the size of a database
  • Optimize a database
  • Clean up a database

Applicable Scenarios

You may need to lean about database maintenance if you are a system administrator and you need to monitor and clean up the database on a regular basis so that it has sufficient space for users to perform needed operations.

Monitoring of the Database Size

You use the Space Usage (SM203525) form to view the system calculations of the used space and the space limit defined by your license. Also, you can review the list of all tenants and snapshots, and view the tables that are consuming a lot of database space. This can help you to identify the actions you should take to reduce the size of the database, if needed.

Attention:
The statistics of using database space, which are displayed on the Space Usage form, do not include data related to the System tenant. The statistics includes size of indexes.

Also, we recommend that before you copy a tenant or take, import, or restore a snapshot, you make sure that you have enough database space available for your instance by clicking View Space Usage on the More menu of the Tenants (SM203520) form.

Cleanup of a Database

To clean up the database so that it has sufficient space for users to perform the needed operations, you can remove the unnecessary tenants (unlicensed or test tenants) and snapshots by using the Delete Snapshots and Tenants (SM503000) form. You can open this form by clicking Optimize Database on the More menu of the Tenants (SM203520) form. You can also select a row with a snapshot you want to remove on the Snapshots tab of the Tenants form and then click Delete on the table toolbar. The system opens the Delete Snapshots and Tenants form, and in the Action box, you can select what exactly (that is, tenant, snapshot, or orphaned snapshot) you want to delete in order to clean up the database.

On the Tables tab of the Space Usage (SM203525) form, you can view the list of tables with data related to all tenants and snapshots in your instance, including the size of each table in the MYOB Acumatica database. The following tables generally use most of the database space of the MYOB Acumatica instance:

  • UploadFileRevision: Contains information about the database size used for attachments. To find the unused attachments so that you can delete them, you use the Search in Files (SM202520) form. For more information, see Attachments: File Maintenance.
  • AUScheduleHistory: Contains information about the database size used for saving the history of the schedules. To review the forms where the automatic processes are scheduled, you use the Automation Schedule Statuses (SM205030) form. To delete the history of a particular scheduled process, you use the Automation Schedule History (SM205035) form. For details, see Automated Processing: General Information.
  • 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. For details, see Managing Field-Level Auditing.