Monday, 11 April 2016

Clearing lost files and old revisions

Hi All,

When you use any database for long time, it become big, heavy and full of different garbage and test data. And Acumatica is not exception here. So every time when you want to back up it and restore on different server for testing/investigation, you have to wait for long time.

In this article I want to share with you one script that can help you free some extra space in your database. This will minimize database size, backups size and backup/restoration speed.
To free some space we want to clear some unused, extra or unnecessary files in the database. This script will:
  1. Delete all files what is not assigned to any entity. You may know that files in Acumatica does not have separate access rights. Rights to files should be evaluated by linked entity. If there is not linked entity this file is unassigned and not visible for everyone. So we need to delete this file to save database space.
  2. Delete all reversions except last one. This may help you save database size, but you will lose all history of the files
  3. Delete all files records without revisions. Sometimes you have file with no data - this is garbage that should be deleted.
This script should be applied only on test/extra environment. Do not do it for production database, you may loose some important information.
Do and validate backup before clearing data.
Think and check everything twice.

If you still want to clear files and you need a script, lets go inside this article.
DISCLAIMER: Deletion of files is dangerous operation. You may loose your data forever with no option to restore. This is generic script and may work differently is some sort of conditions and situations, so you are using it on your own risk.

Do a backup before executing this script.

SQL Code Snippet:

Have a nice clearing!

No comments: