Monday, 19 October 2015

Analyse Size of Acumatica Database

Hi Everyone,

Sometimes, when you have big Acumatica database, you might want to know how much space does it taken by what table.

This script can help you:

The result of this script on my demo database is here:
Samples of heavy tables in Acumatica
You can see that I have highlighted some tables that contains a lot of data. Actually some of them does not contains important information.
Let me point you on some tables that you can archive and clear if you aware about space. This can be helpful if you use cloud hosting and the size of database is not cheep.
  • Logs Tables
    • LoginTrace – all login, logout, screen opening, session expired, customization published events
    • EMailSyncLog – all history of exchange synchronization
    • LogRecord – all history of exception logs
  • Performance Monitor Log –  et of tables that save trace of requests, sql querries, memory usage. This can be helpful for Acumatica support and performance issue investigation, but after that you can clear it.
    • SMPerformanceInfo
    • SMPerformanceInfoSQL
    • SMPerformanceInfoSQLText
    • SMPerformanceInfoStackTrace
    • SMPerformanceInfoTraceEvents
    • SMPerformanceInfoTraceMessages
    • SMPerformanceSettings
    • SMPerformanceValues
  • Historical Information
    • AUScheduleHistory – all history of scheduler execution
    • UPHistory – all history of application update
    • AUNotificationHistory – all history of notification operations.
  • AuditHistory – This is field level audit log. Some times it can be too big, if you use audit a lot. It may be useful to archive this table to file system and clear it in database.
  • UploadFileRevision – This table contains binary data of all files and images that have been uploaded and attached to different entities and screens. Usually this table is huge. Sometimes you can clear old files to save space. Also you store a copy of big database for testing purposes, you can clear this table.
  • SYData – Here you can find all prepared data for import/export scenarios. It can be easily cleared after completing all import/export operations.
Have a nice maintenance!

No comments: