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

Question

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?

Answer 1

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