8

Find Out Who is Wasting Space in Your Mailbox File

Posted by

Is your Mailbox file huge and you’re not sure why? This SQL Query will help you figure out who the culprit is:

SELECT
userid, folder, count(*)
FROM
MailBox
WHERE folder not in ("X-GM-INBOX", "Sent", "Filed", "X-GM-DRAFTS", "X-GM-FOLDERS", "X-GM-GROUPS", "X-GM-ICALINFO", "X-GM-OUTBOX", "X-GM-PROP-HTMLTAB", "X-GM-HTMLTAB", "X-GM-RULES", "X-GM-TEMPLATES", "X-GM-SUBSENT", "X-GM-SUBFILED", "X-GM-WEBIMPORT", "X-GM-SMIME-CA", "X-GM-TD-ITEMS")
GROUP BY
userid, folder
ORDER BY
userid, folder

Note that the long list of folders in the not in section of the query will rule out the more legitimate locations in the mailbox file for mail to collect. The common results of this query will be Liz in accounting and her TRASH folder with 32,000 messages in it.

For a fun variation, run this one:

SELECT
userid, count(*)
FROM
MailBox
WHERE
folder = "X-GM-OUTBOX"
GROUP BY
userid

…to see who has a bunch of mail in the Outbox that they probably have no idea was never sent.

Related Posts

8 thoughts on “Find Out Who is Wasting Space in Your Mailbox File”

  1. Doug Castell

    Glad you like the article! SO many people have so much junk in their e-mail systems…

    Remember, when you look at the inbox, you only see about 1000 e-mails. So you’ll need to select all and delete them a number of times to clear some of that stuff in all likelihood.

    As for the many duplicates of incoming mail issue, it’s a common one and can be linked to a number of different problems such as making the auto-retrieve interval too short, not setting auto-delete, working with a buggy build of GoldMine, working with a buggy build of MS Exchange server and/or any combination of the above…

    If the duplicated messages seem to have recent dates, you might want to track down and correct that issue as well.

  2. Distraught

    oh joy … 27,441 messages in Trash and 17,818 in the Inbox and each message seems to be duplicated at least 20 times. Looks like I have a lot of Delete button clicking to do. Thanks for the query!

  3. GeorgeG

    Hi Doug, thanks for this very useful query. Found out that some of our users have close to 5000 emails in their trash cans!

  5. gmkarl

    Thanks for this excellent Query. I was able to clear nearly 20,000 from old users trash cans.

    A number of folders have come up with a weird folder name like
    X-GMRCDD0UII&5K

  6. Doug Castell

    With SQL Reporting services and the GoldMine 7 integration with it, one could schedule a report with this information in it.

    If you don’t sync, you could simply run a delete query for the trash items. If, however, you do sync, you’ll need to do it via the GoldMine interface — and I can’t think of any other way to do it than logging in as the user. 🙁

  7. ChasL

    Thanks for these useful SQL queries, it shows the MD is the biggest culprit. I could force his trash can to empty via preferences but am loathe to do so. Is it possible to run the query automatically (Scheduled task?) so I can send an internal e-mail advising personnel to empty their trash cans?

    I also discovered trash from previous employees whose account had been deleted, luckily recreating the account with the same name enabled me to empty the offending trash can. Is there a way to empty another users trash can without logging in as them?

    Cheers, Chas

  8. Justin

    Wow this is so useful! Thanks Doug.

    I ran it on a clients system and found that almost all of the users don’t know how to empty the trash.

    So on this system of 10 or so users I am able to delete 6000 out of 62000 items immediately. An almost 10% saving.

    On another clients I discovered users had created elaborate file structures for emails despite my advice not to do that. So more training required there.

    Now if only I could work out how to determine the numbers of duplicate emails in an individuals mailbox? I know this is a big big issue at a clients.

Leave a Reply