Announcement

Collapse
No announcement yet.

Mirth 3.2 - SQLserver - huge database

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mirth 3.2 - SQLserver - huge database

    Hi guys, I'm getting crazy trying to figure out how to set up SQLserver mirthdb to prevent a huge database.
    At this point I put all my channels under production and on pruner tab the following:

    -Prune metadata older than 10 days
    -Don't allow messages archiving.

    The mirthdb is still growing fast 1.5GB in three days.

    Any advice to improve these settings?

    Thanks

  • #2
    Be sure to turn ON the pruner: Settings > Data Pruner > Enable

    Also, be advised, there are ways to setup databases that once they take up XX space, they will not shrink on their own, even when the records are deleted.

    -= Jack Haines : Founder/CEO of Healthcare Integrations, LLC
    -= [email protected]
    -= Mirth Connect (Advanced)-certified
    -= Gold member of HL7.org
    -= Available for Mirth Connect channel development and consultation! Schedule a FREE call with me at https://calendly.com/jackhaines

    Comment


    • #3
      If messages are being pruned correctly, once you reach the 10 day mark, the database should stop growing. That's assuming you don't add additional channels or see a large increase in message volume.

      In the "Content" section, did you select "Prune when message metadata is removed"?
      Best,

      Kirby

      Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

      Appliance Version 3.11.4
      Mirth Connect Version 3.8.0
      Java Version 1.6.0_45-b06
      Java (64 bit) Version 1.6.0_45-b06
      Java 7 (64 bit) Version 1.7.0_151-b15
      Java 8 (64 bit) Version 1.8.0_181-b13
      PostgreSQL Version 9.6.8

      Comment


      • #4
        Same problem

        Hi,

        We're facing the same problem on SQL Server too.

        We were hoping to keep messages for 30d but db would have used something like 800-1000GB data in a near future ...

        Problem is by default SQL doesn't offer any compression feature unless you pay the price for it (Enterprise Edition). That still would be a non-sense because you would then have to apply compression on each table : you deploy a chanel, you then need to call your DBA to activate compression on channel's table. Good thing to ... forget about it until it's too late and your disk is full.

        MySQL have same problem, compression must be activated on each table too. No idea about Oracle. PostGre compresses data by default, this means that the same Mirth installation on PG would need 3-5x less disk space than SQL Server.

        Tom
        Tom

        Comment


        • #5
          In SQL, and pretty much all databases, just because your prune/delete/remove records, doesn't mean that space is recovered. The data is gone, but the database space allocated for the records is still there unless you do something.

          In an ideal world - you'd create your DB with plenty of free space to grow into. You would allow this free space to be there and not strive to give it back and keep your total size right at your used size.. Why? Because your database will eventually grow again.. Then you'll shrink again.. And you'll be stuck in this horrible pattern of useless shrinks followed by growths - and the entire time you'll be increasing your index fragmentation.


          However, if you're positive you want to reduce the data file size, then you can try to run a DBB ShrinkFile on it, but be aware:

          It causes massive index fragmentation.

          Affects performance WHILE it's running and AFTER it's run (one exception - the WITH TRUNCATEONLY option, if you're just removing free space at the end of the file).

          The only way to remove index fragmentation without causing data file growth again is to use DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE.

          Comment

          Working...
          X