Deleting a specific user's folder in Windows server

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
craigberry
New user
New user
Posts: 8
Joined: 2019-06-13 10:44

Deleting a specific user's folder in Windows server

Post by craigberry » 2019-12-02 12:57

We have a user with 40gb of data in his deleted messages folder and it's crippling our server - how can I empty this at windows / database level without using imap or is there a clever way of doing this?

Thanks in advance,

User avatar
katip
Senior user
Senior user
Posts: 732
Joined: 2006-12-22 07:58
Location: Istanbul

Re: Deleting a specific user's folder in Windows server

Post by katip » 2019-12-02 16:33

there is a good script here deleting x-days old mails in certain IMAP folders.
https://www.hmailserver.com/forum/viewt ... 21&t=28139
you may want to try it for a single account or perhaps server wide at all for future.
Katip
--
HMS 5.7.0 x64, MariaDB 10.4.10 x64, SA 3.4.2, ClamAV 0.101.2 + SaneS

User avatar
jimimaseye
Moderator
Moderator
Posts: 8309
Joined: 2011-09-08 17:48

Re: Deleting a specific user's folder in Windows server

Post by jimimaseye » 2019-12-02 23:31

Also try going in to hmailadmin' go to the FOLDERS (under advanced within his account) and right click. Isn't there an 'empty folder' option on it?

(But the script katip directed you to is very effective) and tailorable.)

[Entered by mobile. Excuse my spelling.]
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
Dravion
Senior user
Senior user
Posts: 1614
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Deleting a specific user's folder in Windows server

Post by Dravion » 2019-12-03 02:49

User Data is allways a combination of Database entries and
folder / *.eml files within hMailServees DATA Folder.
All Email attachements are directly embedded as Base64 encoded binary data inside the *.eml files and consuming the most Harddisk space per User.

However, to delete all Emails for a specific User without interrupting or stopping hMailServer you need to delete the
Database records while hMailServer is running via DCOM API.This can be done by a VBScript or my hSQLPad Utility
which lets you execute SQL DELETE commands directly against any hMailServer connected Database. In this case you need to SQL DELETE all DB records within the hm_messages table by the specific UserID you want
to get rid of. In this case hMailServer will automatically
delete all linjed *.eml files correctly vby itself at once.

You can use my Utility if you run a 32-Bit version of hMailServer

https://www.hmailserver.com/forum/viewtopic.php?t=31606


PS:
Stay away of doing the above illustrated method by using
MySQL Workbench or any other direct SQL Database utility
at all! Only if you delete the DB records via hMailServers DCOM API you can be sure all linked *.eml files inside the DATA folder are correctly deleted as well.

craigberry
New user
New user
Posts: 8
Joined: 2019-06-13 10:44

Re: Deleting a specific user's folder in Windows server

Post by craigberry » 2019-12-03 17:43

Thanks for the replies. We seem to have uncovered literally millions of emails in the users' deleted items folder and it's beyond the capability of hmail/imap to cope with these numbers.

Will look at your suggestions and have a think!

Cheers!

palinka
Senior user
Senior user
Posts: 1562
Joined: 2017-09-12 17:57

Re: Deleting a specific user's folder in Windows server

Post by palinka » 2019-12-03 18:16

jimimaseye wrote:
2019-12-02 23:31
Also try going in to hmailadmin' go to the FOLDERS (under advanced within his account) and right click. Isn't there an 'empty folder' option on it?
Yes. I just had a look and confirmed. So for a single account with a gorillion messages in the deleted items folder, this option makes the most sense.

User avatar
katip
Senior user
Senior user
Posts: 732
Joined: 2006-12-22 07:58
Location: Istanbul

Re: Deleting a specific user's folder in Windows server

Post by katip » 2019-12-03 20:18

millions of messages... i don't know about your hardware but it will take ages to remove them with clean methods. very likely your entire system will hang. so, a very dirty suggestion if that account has a reasonable amount of messages other than those in trash:

copy messages to a temporary account.
stop HMS service. go to database.
first, find accountid of that guy. then find all folder ID's of this account in hm_imapfolders.
delete all records in hm_messages and hm_message_metadata where messagefolderid (metadata_folderid respectively) = folder_ID_1 OR folder_ID_2 OR folder_ID_3 OR...
do an OPTIMIZE TABLE hm_message_metadata and OPTIMIZE TABLE hm_messages

go to file system. del path_to\data\domain\account\*.* /s /q
even though directly on filesystem, this may take also a very long time with common file managers (and command line too AFAIK) which first enumerate everything and then start with operation. i recommend Fastcopy (freeware) which starts the operation immediately blind without enumerating anything (although primarily a copy tool, it has a Delete mode too. warning!!! make some tests first!!). you save half the time about.

start HMS service, delete that account, recreate it again, restore messages.

all at your own risk! i may be talking nonsense.
Katip
--
HMS 5.7.0 x64, MariaDB 10.4.10 x64, SA 3.4.2, ClamAV 0.101.2 + SaneS

craigberry
New user
New user
Posts: 8
Joined: 2019-06-13 10:44

Re: Deleting a specific user's folder in Windows server

Post by craigberry » 2019-12-04 11:37

Thanks Katip - your suggestion is so far above my pay grade I wouldn't be confident doing it. We don't really mess about with advanced hmail settings or scripts.

We do have good database skills so we will have a think...

Regards,

User avatar
jimimaseye
Moderator
Moderator
Posts: 8309
Joined: 2011-09-08 17:48

Re: Deleting a specific user's folder in Windows server

Post by jimimaseye » 2019-12-04 12:02

craigberry wrote:
2019-12-04 11:37
We don't really mess about with advanced hmail settings or scripts.
Seriously, in hmailadmin there is a button called 'Empty Folder'. How hard can it be to press it?
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
katip
Senior user
Senior user
Posts: 732
Joined: 2006-12-22 07:58
Location: Istanbul

Re: Deleting a specific user's folder in Windows server

Post by katip » 2019-12-04 12:20

jimimaseye wrote:
2019-12-04 12:02
Seriously, in hmailadmin there is a button called 'Empty Folder'. How hard can it be to press it?
IMO the problem is millions of files on system.
clicking on "Empty Folder" for sure will start a process, but i suspect when and how it ends - if ever.
Katip
--
HMS 5.7.0 x64, MariaDB 10.4.10 x64, SA 3.4.2, ClamAV 0.101.2 + SaneS

palinka
Senior user
Senior user
Posts: 1562
Joined: 2017-09-12 17:57

Re: Deleting a specific user's folder in Windows server

Post by palinka » 2019-12-04 14:35

katip wrote:
2019-12-04 12:20
jimimaseye wrote:
2019-12-04 12:02
Seriously, in hmailadmin there is a button called 'Empty Folder'. How hard can it be to press it?
IMO the problem is millions of files on system.
clicking on "Empty Folder" for sure will start a process, but i suspect when and how it ends - if ever.
Just initiate it in the evening. It will chug through it overnight. I wouldn't expect any problems unless you really REALLY need to use the admin while it's in process. Otherwise it will just work through them one by one until it's done.

Of course, make sure anything that could interrupt it is turned off, like a backup routine or automatic updates or whatever.

User avatar
jimimaseye
Moderator
Moderator
Posts: 8309
Joined: 2011-09-08 17:48

Re: Deleting a specific user's folder in Windows server

Post by jimimaseye » 2019-12-04 17:40

katip wrote:
2019-12-04 12:20
jimimaseye wrote:
2019-12-04 12:02
Seriously, in hmailadmin there is a button called 'Empty Folder'. How hard can it be to press it?
IMO the problem is millions of files on system.
clicking on "Empty Folder" for sure will start a process, but i suspect when and how it ends - if ever.
Having read your suggestion. it seems it will delete all messages from the database for that account folder but will not delete the physical files off the disk. It would still requre a Data Directory Syncroniser (in 'delete if not in database' mode) to clear down the files and regain diskspace - something that itself will still take an age. With this in mind there is no "quick" way to acheive what he requires. Your procedure could be speeded up slightly by renaming the ACCOUNT folder of the user (instead of backing it up, and restoring it after recreating the account)
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
katip
Senior user
Senior user
Posts: 732
Joined: 2006-12-22 07:58
Location: Istanbul

Re: Deleting a specific user's folder in Windows server

Post by katip » 2019-12-04 20:09

jimimaseye wrote:
2019-12-04 17:40
katip wrote:
2019-12-04 12:20
jimimaseye wrote:
2019-12-04 12:02
Seriously, in hmailadmin there is a button called 'Empty Folder'. How hard can it be to press it?
IMO the problem is millions of files on system.
clicking on "Empty Folder" for sure will start a process, but i suspect when and how it ends - if ever.
Having read your suggestion. it seems it will delete all messages from the database for that account folder but will not delete the physical files off the disk. It would still requre a Data Directory Syncroniser (in 'delete if not in database' mode) to clear down the files and regain diskspace - something that itself will still take an age. With this in mind there is no "quick" way to acheive what he requires. Your procedure could be speeded up slightly by renaming the ACCOUNT folder of the user (instead of backing it up, and restoring it after recreating the account)
no, pls read next paragraph about deleting millions of files too
go to file system. del path_to\data\domain\account\*.* /s /q
even though directly on filesystem, this may take also a very long time with common file managers (and command line too AFAIK) which first enumerate everything and then start with operation. i recommend Fastcopy (freeware) which starts the operation immediately blind without enumerating anything (although primarily a copy tool, it has a Delete mode too. warning!!! make some tests first!!). you save half the time about.
if all goes well, there is nothing left at all from that account except the account itself, but important (non-trash) mails which were copied to a temporary account as first step in the beginning. final step is remove/recreate account and restore mails from temp account by IMAP copying.

i'm 100% sure that deleting with Fastcopy (or a similar tool) on filesystem level without enumaration will save at least half of the time compared to "legal" process (lookup table row, read it, find file, delete it, delete row, start over...).
i may be wrong but know well how painful it is to delete some 100K files on disk, let alone millions.
very likely this job will end up in either way with some errors on file system. a chkdsk hopefully will fix all if nothing serious happens. but this is a different subject.
Katip
--
HMS 5.7.0 x64, MariaDB 10.4.10 x64, SA 3.4.2, ClamAV 0.101.2 + SaneS

User avatar
jimimaseye
Moderator
Moderator
Posts: 8309
Joined: 2011-09-08 17:48

Re: Deleting a specific user's folder in Windows server

Post by jimimaseye » 2019-12-05 00:31

I don't get it.
copy messages to a temporary account....
This will take (as long as the time your trying to avoid spending time on)

....find accountid of that guy. then find all folder ID's of this account in hm_imapfolders.
delete all records in hm_messages and hm_message_metadata where messagefolderid (metadata_folderid respectively) = folder_ID_1 OR folder_ID_2 OR folder_ID_3 OR...
do an OPTIMIZE TABLE hm_message_metadata and OPTIMIZE TABLE hm_messages
Delete references to messages in the trash folder from the database. Got it.

....go to file system. del path_to\data\domain\account\*.* /s /q
So.. delete all email files. Will take an age even at windows level. (This would be quicker by simply doing a rename of the account folder).

start HMS service, delete that account, recreate it again, restore messages.
Restore from where? The temporary backup account? Another copy (now of remaining required folders) albeit slightly shorter. But it still leaves the initial mammoth store of the initial emails copied to the temporary account which requires deleting. And deleting this will take an age (still) even at windows level.

So how are your saving time over the 'empty folder' option?

[Entered by mobile. Excuse my spelling.]
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
katip
Senior user
Senior user
Posts: 732
Joined: 2006-12-22 07:58
Location: Istanbul

Re: Deleting a specific user's folder in Windows server

Post by katip » 2019-12-05 06:01

jimimaseye wrote:
2019-12-05 00:31
I don't get it.
copy messages to a temporary account....
This will take (as long as the time your trying to avoid spending time on)

no, i meant only messages which are not trash, assuming they aren't millions too :)
....go to file system. del path_to\data\domain\account\*.* /s /q
So.. delete all email files. Will take an age even at windows level. (This would be quicker by simply doing a rename of the account folder).
yes, but so or so we must get rid of them. pls read further about Fastcopy suggestion.
start HMS service, delete that account, recreate it again, restore messages.
Restore from where? The temporary backup account? Another copy (now of remaining required folders) albeit slightly shorter. But it still leaves the initial mammoth store of the initial emails copied to the temporary account which requires deleting. And deleting this will take an age (still) even at windows level.

So how are your saving time over the 'empty folder' option?

[Entered by mobile. Excuse my spelling.]
yes from temp account. as i said, we backed up only mails which aren't trash and assuming they are in a managable amount. if they are also another millions, forget about all what i say.

once again, short version:
1. copy same folder structur and mails (EXCEPT THAT TRASH FOLDER & MAILS IN IT) to a temp account.
2. delete all references to that account in hm_messages and hm_message_metadata.
3. find a way to delete all mails of that account on filesystem level (Fastcopy will do it in about 50% less time).
4. delete/recreate account, restore IMAP structure and mails from temp account.

renaming account folder trick is good. if you don't care about millions of trash file on disk forever, that'd be a more simple solution.
Katip
--
HMS 5.7.0 x64, MariaDB 10.4.10 x64, SA 3.4.2, ClamAV 0.101.2 + SaneS

User avatar
jimimaseye
Moderator
Moderator
Posts: 8309
Joined: 2011-09-08 17:48

Re: Deleting a specific user's folder in Windows server

Post by jimimaseye » 2019-12-05 10:16

once again, short version:
1. copy same folder structur and mails (EXCEPT THAT TRASH FOLDER & MAILS IN IT) to a temp account.
2. delete all references to that account in hm_messages and hm_message_metadata.
3. find a way to delete all mails of that account on filesystem level (Fastcopy will do it in about 50% less time).
4. delete/recreate account, restore IMAP structure and mails from temp account.
Got you. Now it's clear. :)

[Entered by mobile. Excuse my spelling.]
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

craigberry
New user
New user
Posts: 8
Joined: 2019-06-13 10:44

Re: Deleting a specific user's folder in Windows server

Post by craigberry » 2019-12-05 14:32

palinka wrote:
2019-12-04 14:35
katip wrote:
2019-12-04 12:20
jimimaseye wrote:
2019-12-04 12:02
Seriously, in hmailadmin there is a button called 'Empty Folder'. How hard can it be to press it?
IMO the problem is millions of files on system.
clicking on "Empty Folder" for sure will start a process, but i suspect when and how it ends - if ever.
Just initiate it in the evening. It will chug through it overnight. I wouldn't expect any problems unless you really REALLY need to use the admin while it's in process. Otherwise it will just work through them one by one until it's done.

Of course, make sure anything that could interrupt it is turned off, like a backup routine or automatic updates or whatever.
If only it were that simple. Whenever we go to empty (or delete) folder Hmailserver crashes - it just doesn't seem to cope with the sheer size of the folder.

palinka
Senior user
Senior user
Posts: 1562
Joined: 2017-09-12 17:57

Re: Deleting a specific user's folder in Windows server

Post by palinka » 2019-12-05 15:03

craigberry wrote:
2019-12-05 14:32
If only it were that simple. Whenever we go to empty (or delete) folder Hmailserver crashes - it just doesn't seem to cope with the sheer size of the folder.
Are you sure it's actually crashing? The admin freezes up when doing operations like this. That's why I said initiate in the evening. Let it run. It will work.

However you may have orphans if you already attempted and aborted this.

User avatar
jimimaseye
Moderator
Moderator
Posts: 8309
Joined: 2011-09-08 17:48

Re: Deleting a specific user's folder in Windows server

Post by jimimaseye » 2019-12-05 15:10

palinka wrote:
2019-12-05 15:03
Are you sure it's actually crashing? The admin freezes up when doing operations like this. That's why I said initiate in the evening. Let it run. It will work.

However you may have orphans if you already attempted and aborted this.
+1
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

Post Reply