Page 1 of 1

hm_messages Cleaning Script

Posted: 2008-09-23 17:18
by mrpepik
Hi Everyone,

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.

Thanks

MrPepik

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