Recently we ran into an issue where our hm_messages table grew to over 650M in size and from another forum post I found, hMailServer has an issue with IMAP once the size of the hm_messages grows over 300M in size. As we are converting all of our users to IMAP, this was a major issue. We also use a Catch All email address to keep a copy of all mail sent to and from our domains. So needless to say our managers were ready to move away from hMail to another platform. Once I found the post and wrote a quick delete and optimize script for several email accounts, the catch all and of course do-not-reply, which some people seem to want to respond to, strange I know but they do, *S*, the script took the 650M+ table down to 92M. Which has not caused any problems with IMAP since.
Attached is the latest and greatest for the script. I have put in a few niceties, such as checking to make sure the backup email folder exists, creating a backup of the hm_messages table before doing any deletes,
Take a look at it and if you find it is useful, please do so.
I have enjoyed hMailServer and would love to see a version of it for Linux, but it is a great Windows E-Mail Server.
Ok, I tried to post the code as an attachment, but none of the extensions I used worked. *G* So here is the code:
Code: Select all
@echo off SETLOCAL ENABLEDELAYEDEXPANSION REM Clear the screen cls REM Hope this works for you and enjoy REM Change the following Variables to Match Your hMail Server Installation set dbname=DATABASENAME set dbuser=DATABASEUSERNAME set dbpass=DATABASEPASSWORD set domain=PRIMARYDOMAIN.com REM Enter a single email account, such as your catch all account, or multiple accounts with a space between REM each email account name. Examples below, replace as needed set eid=archives do-not-reply REM Set the current date to backup the hm_messages table set Today=%date:~10,4%-%date:~4,2%-%date:~7,2% REM Stop hMailServer Process net stop hMailServer REM Check to see if C:\hmailserver_backup folder exists else create it IF NOT EXIST C:\hmailserver_backup mkdir C:\hmailserver_backup REM Backup hm_messages table before doing maintenence mysqldump -u %dbuser% -p%dbpass% -e %dbname% hm_messages > C:\hmailserver_backup\hm_messages_backup-%Today%.sql REM This will kick off the deletion of all entries from the hm_messages table for each account you specified for %%i in (%eid%) do ( set emailid=%%i Call :LOOP ) REM Optimize the hm_messages table to remove all white/empty space and reduce the size to REM hopefully under the 300M mark where some systems start having issues mysql -u %dbuser% -p%dbpass% -e "OPTIMIZE TABLE %dbname%.hm_messages;" REM Start hMailServer net start hMailServer REM Finish all other tasks for %%i in (%eid%) do ( set emailid=%%i Call :LOOP1 ) :FINISHER GOTO :EOF :LOOP1 REM Check to make sure email folders exist in C:\hmailserver_backup IF NOT EXIST c:\hmailserver_backup\%emailid% mkdir c:\hmailserver_backup\%emailid% REM Copy the backup of the Email Folders to the Backup Directory and then Remove the original REM backup in the hmailserver backup directory structure to keep it nice and clean xcopy "C:\Program Files\hMailServer\Data\%domain%\%emailid%.bac" c:\hmailserver_backup\%emailid% /E /X >> C:\hmailserver_backup\hm_messages_cleanup_info-%Today%.txt rmdir /S /Q "C:\Program Files\hMailServer\Data\%domain%\%emailid%.bac" GoTo :FINISHER :LOOP REM Print out the current email account name echo %emailid% > C:\hmailserver_backup\hm_messages_cleanup_info-%Today%.txt REM Get the current entry counts from mysql hm_messages table, then delete all of the entries, REM then perform another count to verify all entries were removed mysql -u %dbuser% -p%dbpass% -e "select count(*) from %dbname%.hm_messages where messagefilename like '%%%domain%\\\\%emailid%%%'; delete from %dbname%.hm_messages where messagefilename like '%%%domain%\\\\%emailid%%%'; select count(*) from %dbname%.hm_messages where messagefilename like '%%%domain%\\\\%emailid%%%';" >> C:\hmailserver_backup\hm_messages_cleanup_info-%Today%.txt REM Rename the email folder you removed all entries for with a .bac extension and then create REM a new folder to catch email, doing this speeds up the time to restart the hMailServer. The REM backup of these folders will happen after hMailServer has been restarted. rename "C:\Program Files\hMailServer\Data\%domain%\%emailid%" %emailid%.bac mkdir "C:\Program Files\hMailServer\Data\%domain%\%emailid%" :EOF exit