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.

Clearing lost files and old revisions in Acumatica
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!

6 comments:

neo said...

Is there a way to restore deleted items (approval map, invoice, receipts, etc.) in Acumatica or at least restore from a DB backup?

As per checking, items are not actually deleted in the database as they just seem do be hidden.

Sergey Marenich said...

Hi Neo,
For some records you have special column - DeletedDatabaseRecord that is basically a flag that is raised when record is deleted. You can restore records by updating of this flag to false in database. There is no way to do it from UI.
Please check this article also: http://asiablog.acumatica.com/2015/10/database-special-columns.html
and this one: https://stackoverflow.com/questions/37121174/purge-deleteddatabaserecords-from-database

neo said...

Thanks for the response Sergey!

Unfortunately, EPAssignmentMap and EPAssignmentRoute don't have DeletedDatabaseRecord columns.
Unless I am looking at the wrong location for Approval Maps.

Anyhow, it would be great if Acumatica can implement management of this in the interface since the data is still there once deleted anyway. I think the name "Recycle Bin" will be a great name for this feature? :)

Sergey Marenich said...

Hi Neo,
Yes you are right. Not all places contains DeletedDatabaseRecord to optimize database size and performance. Basically not to keep garbage.
Your idea with recycle bin is very nice and I'll share it with development team.
Please note that you can submit more ideas on http://feedback.acumatica.com . Acumatica team always monitors it and prepare road-map based on it.

neo said...

Thanks again Sergey!

In this case, the tables I mentioned doesn't have DeletedDatabaseRecord column but the deleted data are still there and I am very sure with that. So that doesn't help in keeping the garbage out as you are suggesting. I wonder how will that be overtime especially for those who manage multiple transactions with a bunch of users.

As for the Recycle Bin, I submitted an idea and I hope this can be considered.
I have to apologize though as I have to refer to 2 of your blog entries here, I hope you won't mind. :)

https://feedback.acumatica.com/ideas/ACU-I-1459

Sergey Marenich said...

Neo,
Please contact me by skype, we can see what is going wrong with your Approval.