2

How can I tell how much disk space each channel uses for message and metadata storage with my Mirth Server using SQL Server as the backing database?

Mitch
  • 21,223
  • 6
  • 63
  • 86
  • 1
    This is not a blogging platform.You "answered" your own question immediately after posting. – SMor Nov 19 '19 at 22:12
  • 2
    @SMor, while this is not a blogging platform, answering your own question is [explicitly encouraged on StackOverflow.](https://stackoverflow.com/help/self-answer) – Mitch Nov 19 '19 at 23:11
  • Agree with @SMor, this is not a blogging plaform... Posting answers make sense if you don't know them beforehand when asking. Also it looks like posting simultaneous questions and answer is a pattern of yours, as it can be seen [here](https://stackoverflow.com/questions/57504594/how-can-i-use-a-chromiumwebbrowser-when-collapsed), [there](https://stackoverflow.com/questions/52414239/how-can-i-use-http-sender-to-submit-a-client-certificate-without-the-ssl-manager), and [so on](https://stackoverflow.com/questions/23281434/t4-reports-compiling-transformation-invalid-token-this-in-class-struct)... – GMB Nov 20 '19 at 00:54
  • 1
    @GMB, regardless of whether you agree with SMor, it is an encouraged practice - with an option to answer your own question before you even post the question to the public. If you don't like it, [take it up on Meta - not with me.](https://meta.stackoverflow.com/). I'm just sharing an answer to a concise reproducible and common problem to a problem with a piece of software used by developers - fairly "in scope" for StackOverflow regardless of how long and who answers my question. Would you rather I not share the information? Is it a poor fit for the Q/A format? – Mitch Nov 20 '19 at 03:56
  • 3
    See also: ["It’s also perfectly fine to ask and answer your own question, as long as you pretend you’re on Jeopardy! — phrase it in the form of a question." ~Jeff Atwood (Co-founder of StackOverflow)](https://stackoverflow.blog/2011/07/01/its-ok-to-ask-and-answer-your-own-questions/) – Mitch Nov 20 '19 at 04:03

1 Answers1

3

You can use the following query to identify offending channels and metadata tables:

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
Mitch
  • 21,223
  • 6
  • 63
  • 86
  • 1
    @agermano, I wish I knew why there was a downvote :). It was tested on SQL Server 2016 with Mirth Connect 3.5.1. Allowed us to see a channel with the wrong archive settings that was consuming 38GB. – Mitch Nov 21 '19 at 02:10
  • 1
    for reference here is a similar query for postgres - https://gist.github.com/MichaelLeeHobbs/67980d165fc68880eb2ab283c673244b – Freiheit Jan 11 '23 at 14:34