What are people doing regarding growing database of old unused records from several years ago? It appears Sugar didn’t address this with some sort of function to archive all records older than x years old? Is everyone really just letting their number of database records grow indefinitely?
Hi Jeff
SuiteCRM soft deletes the records which means they are Marked as deleted but still stay in the Database for One Month. Their is a Scheduler Job “Prune Database on 1st of Month” which runs every month and cleans up those soft deleted record to clear up the DB.
Make sure your cron jobs are running. Once setup correctly, it will clean up DB itself.
what cherub-chum said is true, but that scheduler is disabled by default, you need to enable it
best regards
You are talking about permanently deleting DELETED records. To go through and try to delete THOUSANDS of old records via the user interface is too difficult. I’m talking about a function that AFTER pruning deleted records, goes through select tables, like cases, tasks, invoices, emails, notes, etc. etc. (excluding contacts, workflow, and other tables that are necessary for ongoing operation) and looks for records say older than 5 YEARS! And exports them to a compressed SQL file and then removes them from the active database.
If you had like 10 years of data without doing this you would have some tables that are GIGANTIC with possibly hundreds of thousands of records which would slow down queries. I am very surprised if there isn’t some third party module that is available that does the archiving?
So you are saying the only thing people can do is remember to periodically manually delete old records in several modules and then run DB prune? That is a terrible crazy method.
I found this great thread here and I wondered something similar. Is there a kind of ‘database maintainer’s guide’ somewhere? Or some kind of ‘best practices’ for SuiteCRM?
This would be great.
My only plan after reading this is to:
- take a DB backup
- Start the ‘prune’ job (by the way - do I have to ‘trick’ it by changing frequency to every 5 hours to get the first one started, or will the first one start right away after I activate it?)
- Hope that it cleans up a lot of the leftover ‘stuff’.
I have a database of over 2gigs which I feel is way too big for the level of activity…
Since this is now my job to figure out, any links on this topic would be greatly appreciated and I don’t mind helping writing some guides in Markdown if there is a missing gap…
1 Like
I’m just replying to my own post here because I think there is some big benefit. What I was searching for ‘may’ be including in this page. I’m not sure but I suspect the forum user who made this is @pgr ? Yes, indeed it was pgr
Although I’m still super inexperienced, I think I will start working through this page and really learning its elements and see if I can get our database a bit smaller. Hope this page helps someone else
https://pgorod.github.io/Database-tables-size/
I’ll do my best to write some tutorials as I figure stuff out…
Big thanks @pgr!
1 Like
Please have a look at this tool for Automation of backup and restore.
https://store.outrightcrm.com/product/suitecrm-backup-restore-manager/
How did it all go?
I checked PGRs article, and ran the query in PhPMyAdmin… Not sure what to do with the information I got from the query. My workflows in SuiteCRM are working but Perform Lucene Index, and AOD are failing at the moment… My Uploads folder is 9GB or so I may be in need of some maintenance. 
@dwaynecasey I’m pretty sure your Lucene Index and AOD optimization are failing are because of memory allocation for the PHP version in WHM OR allowed_cron_users. Unless you have a lot of workflows that run all the time on every record, you won’t have a lot to clean up in the workflows. Cleanup of uploads can be difficult task. Because the files are all coded, you don’t know what file is what in the file manager. You have to kind of manually review your notes and documents modules and try and remove old or irrelevant stuff. It’s even more difficult because you can’t really look by file size to take out the big ones to reduce file space.
1 Like
For the uploads folder, there are articles in my blog which will probably help you.
The first one includes an SQL query that helps you match the “human” file names (e.g. “myReport.xls”) to the coded file names in the file system (e.g. ’ 85c2ee00-a1ce-3c59-55bb-572397afb36e’).
1 Like
pgr wrote in his blog:
Orphan records in relationships
…
You can hunt down these rows with clever SQL.
Does anyone of any examples of these clever SQL Queries?