By using this website, you agree to our Terms of Use (click here)
I'm struggling to keep my database size down (space usage menu). I've setup Azure BLOB external storage and it helped tremendously, but I'm not quite sure what exactly was offloaded. And maybe I'll be paying for files I really don't need. For example, I have a bazillion emails with file attachments in them. They are archived, but they still have the files.
Can I mass delete emails somehow? Can I flip a switch so it doesn't save the attachments? Any ideas?
You can use the Trash Can icon on the All Emails (CO409070) screen to delete emails, but I have a feeling that it only sets the Deleted column in the database table to True and doesn't actually remove the record from the database. At least that would be consistent with how other records get handled in Acumatica.
Are you sure though that email attachments aren't getting pushed to Azure (or whatever external file storage has been setup)? I always assumed that they were, but I haven't actually confirmed.
I think you are right about the "delete flag". I have a test database that only I have access to. The more I deleted emails (which takes foooorrrever by the way) the bigger my database became. That was unexpected.
I still don't know if it dumped email attachments to Azure, but I assume it did. Over the next few days, my Azure BLOB grew and my database dropped from 12 Gigs to 6. I don't want to continually grow my BLOB with unwanted attachments though.
What we really need is a "purge" functionality in Acumatica which, as far as I know, doesn't exist yet.
Why doesn't it exist? My guess is that it's because of Acumatica's young age and the fact that most of their customers were added in the last few years so there aren't many who have run into a need for this.
I suggest adding your vote and, more importantly, commenting with your specific situation, on this feedback idea which (thankfully) has already been marked as "Future Consideration" which means that the Acumatica team is looking at it:
If you need to explain to a client the reason behind the size of their database you can take a snapshot of their data and restore it into a local instance and run the following SQL script. This script will identify the size of each table within the database.
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp
Nice Carl!
In 2018 R2 this is built in, click on System Mgmt --> System --> Space Usage, this page shows the size of your database. The size of your database is determined by the number of tenants, snapshots and transaction volumes.
If you highlight the tenant ID & then click on View tables, this will show you tables that are growing in size and then you can probe into it. If it's SaaS then you need to contact Acumatica to clean those tables other deployments you can determine if it's worth cleaning the data without breaking it 🙂
- Harsha
You haven't seen a purge feature yet have you Harsha? I'm sure it's coming, especially now that more and more Acumatica customers have been running on Acumatica for multiple years.
I missed responding to this topic, with new releases of 2019 R2 purge still hasn't surfaced and we are reaching that threshold in a yr or two as business realizes to store big files of these database ... I'm hoping, acumatica provides an option to customers to choose which files and how they want to store in the database vs storage containers ...
I'd love to see them offer something like a "cold" storage option where you "purge" the data out of the Acumatica database, but into a blob in AWS (especially GL data). Blob storage is so cheap in AWS. And it can still be reported on with BI tools. Maybe even Velixo reports in the future.
Would love to hear thoughts from @gabriel-michaud1 on this.
In my experience in many cases the high database size is due to an overabundance of snapshots or log tables. In the past automation schedule history was the main culprit but in recent versions they added a setting to control the number of executions to keep in the log. PushNotificationErrors is another table that could be purged easily -- you if you don't have direct SQL access you can create a dummy customization project and run a TRUNCATE TABLE script to clean it during publishing. In the example above this table accounts for 50% of the total size of the tenant!
I'd be curious to hear what are the largest database sizes you've seen with Acumatica - one of the companies I work with has a 1tb database!
@gabriel-michaud
I agree the snapshots are double edge swords, it's so easy but it can bloat your database size, and if your snapshot size is over 1GB (not DB) you won't be able to take snapshots due to IIS constraints from what support mentioned ...
The other culprit is audit history/SQL profile log if you turn it on and forget to purge or clean then it will be heavy, third is file storage.
If you allow customers to upload art files then they will blow through the space usage, suggesting them to use AWS/local folder structure is good.
I can't vouch for 1 TB database, it's all relative to resource level, how they store data/files and transaction volume and how you optimize within that space 🙂
We are having Acumatica purge for use through a case. We have 17GB of e-mails that we don't need in Acumatica and deleting wasn't going to help since they would still be in the database.
Good move having Acumatica do the purge. Make them earn their SaaS fee 🙂
Has this ever been solved with newer versions? We just added a user inbox as a system email account as a suggestion by Acumatica, and it jumped by database size from 80 to 95 GB. We've used Acumatica for 6 years and attach a lot of PDF scanned documents to our transactions. But having so many regular emails not attached to an Acumatica transaction is not practical. It should only keep emails that are tied to an activity, right? Why is it keeping a record of every email?
@jwaldron Since you have Acumatica reading the emails from that email address, it has to store them somewhere. You could probably do a database script to find emails that aren't attached to an Activity and then delete them.
You might want to try AugSQL (click here), which allows you to run SQL code and see the results immediately on the screen, even if you're in an Acumatica SaaS Instance. You can also use AugSQL to run SQL code on a schedule so you could have it run nightly to cleanup emails that aren't attached to an Activity.
It's been a moment. Any news on a clean way to kill off emails, files, and audit history? Below is my space usage sorted by table size. Any guidance for any of those top 3 tables would be appreciated.
My understanding is that:
- UploadFileRevision is our attachments and upload revisions
- I have seen some SQL solutions, but would love to avoid executing something so vast
- AuditHistory is our log of changes on tables we've enabled
- I tried using Security Preferences, it seems to do nothing
- AUNotificationTemplate is every email we've ever sent (since 2017)
- I tried both archiving and deleting, seems neither of those actualy shrink/reduce data
@michaelhansen these are exactly the same tables I’m concerned about. We’re on-prem so the storage costs add up.
Yes, please! This is a nagging concern. Thank you!