SugarCRM recently started enforcing SugarCloud managed storage limits. When you get the "Action Required: Your SugarCloud Managed Storage" email from SugarCRM, you can choose to decrease your usage or purchase more storage space. If you need help setting up any of these reports or taking action on the results, please contact us.

The first thing to do is to double check the results of the email by opening Admin -> SugarCloud Insights which returns a page like this:

Notice that the storage space is split into two categories: File System and Database.  We then need to dig further into each of these.

If we are over, then we need to either delete data or buy more data.  If no action is taken, then we will receive automatic invoices for the data overage. Note that there is a discount for pre-paying for storage space. If you would like help reviewing your instance or purging data, please let us know.

Database

To dig into the database, we can run a SQL Query by using an Advanced Report Query.  To do this, open the Reports module dropdown on the Nav Bar and select Manage Advanced Reports. 

Now that you can see Advanced Reports in the Nav Bar, select the Advanced Reports module dropdown and select Create Custom Query.

Please note that you only have to create the query in your instance of Sugar once, but you will need to create the query in every instance you want to review, for example your production instance and test and dev.  On the Create Custom Query screen, paste the following generic SQL into the Custom Query field.  If you have a lot of custom queries in your instance, you may have to scroll down to see the Custom Query creation fields.

SELECT Concat(table_schema, '.', table_name) tablename, Concat(Round(table_rows / 1000000, 2), 'M') numrows, data_length, index_length, (data_length + index_length) total_size FROM information_schema.tables ORDER BY total_size DESC;

Give your query a unique name in the Query Name field and click Save.

Now in the Custom Queries List View, click Run Query for your query.

When you run the query, you can see the total size of each table as well as some other interesting data:

A few notes on the tables:

• The activities and activities_users tables house the Activity Stream data. This data is not necessary for Sugar to function and Sugar has an Activity Stream Purger Scheduler that can be enable to make the tables smaller.
• The …_audit tables house the Audit Log. This data is not necessary for Sugar to function but it is very useful.  If you find a table with a large Audit table, you may want to re-consider what fields you are auditing on that Module.
• The pmse_bpm_flow and pmse_inbox tables house information about running Process Definitions.  These tables can become very large if you use a lot of Process Definitions. These tables can be cleaned up but it is tricky.
• The tracker* tables house the Tracker data. Unless you need Tracker data, Trackers should be disabled and if the tables are large, the data should be purged from these tables.

File System

To investigate the File System, you can create several reports of Attachments that will give use information about who is creating most of the Attachments.

Large Files

We can create a “Large Files” Report by defining our Filter as Notes Files Size Greater than 5000000. 5,000,000 is roughly 5MB.

If you find large files that are no longer needed, deleting them can be a quick way to reduce storage.  If you need help mass deleting Notes, please let us know.

Notes Created by User

We can view how many Notes people are creating by creating a Summation Report.  In this report, I used a File Size Greater Than 1 Filter.  Group By Created by User -> User Name.  Display the Count Summary.  I prefer Vertical Bar Charts.  This report may help you discover if one or two users are creating an excessive number of Notes and if they need training for what to save and what not to save.

Attachment Count & Size by Name

Just like above, create an Advanced Report Query and use the following SQL:

SELECT IFNULL(notes.filename,'') notes_filename

,COUNT(notes.filename) notes__allcount,

sum(notes.file_size)

FROM notes

WHERE notes.deleted=0

 GROUP BY notes.filename

having COUNT(notes.filename) > 100

order by COUNT(notes.filename) desc

This query is useful to show you files that exist in the system many times.  Even though these pictures are small, they can add up to a lot of size usage because there are so many of them.  If we know that image001.png and similar pictures are from email signatures, then we can safely delete them.  If you need help mass deleting Notes, please let us know.