Tips for keeping the database size as small as possible

[Coooz]

Hi,

On https://forums.mirthproject.io/forum/mirth-connect/support/13367-database-size-growing-out-of-contol Nick Rupley gives some very useful remarks on why the database may grow larger than intended.
Since a customer of ours complains about a bloated database (70 Gb, SQL Server), I'm very much interested in similar tips to check or implement that will help to keep the database relatively small.
I know about the message storage slider, message pruning, and the occasional database shrink + index defrag. Are there any other additional best practices/rules of thumb that may be overlooked - for instance, like the ones in the link? I would be most grateful!

Also: said customer uses Mirth Connect 3.5.1. Is it possible that simply upgrading to 3.12.0 will help me here? I wanted to check the release notes of all of the inbetween versions for this, but they cannot be accessed. I get proxy errors instead.

[rjkroll]

I mean, Nick always gives probably the best responses, so if he didn't mention anything else, then probably there is nothing else to mention.

Since they are on SQL, you can try to run this query any maybe spot where the offending channel is and direct your changes/tweaks there to see if you can maintain a steady size in database growth:

SELECT COALESCE(c.NAME, s.Name + '.' + t.Name) as Name, MAX(p.rows) AS RowCounts, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id LEFT OUTER JOIN D_CHANNELS dc on TRY_CAST(SUBSTRING(t.name, PATINDEX('%[0-9]%', t.name), 10) as bigint) = dc.LOCAL_CHANNEL_ID LEFT OUTER JOIN CHANNEL c on c.ID = dc.CHANNEL_ID WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY COALESCE(c.NAME, s.Name + '.' + t.Name) ORDER BY TotalSpaceMB DESC

[Coooz]

Hi @rjkroll,

"if he didn't mention anything else, then probably there is nothing else to mention"
What he mentions applies to the database plus channels of a forum poster. So maybe - hopefully - there's more...

I already came across the SQL you provided. I expect to have access to the customer's channels next week, and then I will certainly give it a go.

Thank you for replying!

[tonygermano]

@Coooz Nick actually did mention several other things in that thread other than what you listed in your original post for those focused on reducing storage usage.

• Look for places where you can use attachments, especially if the individual messages are large and contain big chunks that you can ignore (also mentioned by @jonbartels )
• Watch what you are putting in the mirth maps, and the channelMap in particular. The entire channelMap gets serialized to the database for the current connector and for each destination that follow it.
• Modify the destinationSet instead of using filters in the destinations in most cases

As mentioned in this answer, it's probably best to focus your attention on your largest tables first to see if there's anything you can do in the channels to reduce the data being written to them.

[Coooz]

Useful tips! Thanks @tonygermano .

[jonbartels]

Michael Hobbs wrote an excellent query which shows the size of various MC channels https://gist.github.com/MichaelLeeHobbs/67980d165fc68880eb2ab283c673244b It should a lot of detail about what channels and what data is using the space.

Chris T. wrote an excellent article on using Attachments in MC also - https://forums.mirthproject.io/articles/175046-using-attachments This is valuable for space because for messages MC stores ONE copy as an attachment and re-uses versus passing teh whole message from one connector to the next.

[Coooz]

Thank you @jonbartels . I'll check it out!

[aungktun]

This also works if you are on MS SQL server. https://stackoverflow.com/questions/58942371/get-space-used-by-mirth-per-channel-in-sql-server

…

On Tue, Dec 21, 2021 at 8:17 AM Coooz ***@***.***> wrote: Thank you @jonbartels <https://github.com/jonbartels> . I'll check it out! — Reply to this email directly, view it on GitHub <#4895 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFPLOOTH3FVIH2GWUHLWFZDUSCSAJANCNFSM5JZGANHA> . You are receiving this because you are subscribed to this thread.Message ID: <nextgenhealthcare/connect/repo-discussions/4895/comments/1853690@ github.com>

[Coooz]

Hi @aungktun ,

Yes, thank you - but this the SQL from rjkroll's post. I appreciate you replying though.