Sidebar

How to reclaim disk space after purging messages with a MariaDb/MySql database?

0 votes
1.2K views
asked Jan 27, 2016 by rich-c-2789 (15,250 points)
I have QIE installed using MySql for the back end.  We had a channel that only needed to be ran once.  This channel processed a lot of messages above our normal work load.  After running the channel we purged the messages but the size of the message_queue and system_log tables did not release the space used by this channel.  How can I reclaim the disk space?

1 Answer

0 votes

NOTE: Before using this do the following:

  • o - Make sure there is twice the free space available on disk as the size of the database.
  • o - Stop QIE

 

MySql has the followin command that can be used to reclaim the empty space in the table:

OPTIMIZE TABLE <tablename>

See MySql documentation for your version for additional details: http://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

For MariaDB: https://mariadb.com/kb/en/mariadb/optimize-table/

At a minimum, the following tables may have space that can be reclaimed in the senario given above:

  • message
  • message_queue
  • system_log
  • system_log_blob

 

Also see these related questions

How to shrink MS SQL Server transaction logs for the QIE database?

How do I shrink the h2 database file after removing thousands of errors?

How to set the Recovery Model to Simple in MS SQL Server?

How should I resolve an "Out of Disk Space" error?

How to check the current and max size of the databases on SQL Server

answered Jan 27, 2016 by rich-c-2789 (15,250 points)
edited Dec 13, 2019 by rich-c-2789
...