Old emails are starting to take a lot of space. How can the administrator remove old emails ?

jperreault's Avatar

jperreault

04 Nov, 2014 06:45 PM

Hi,

We have a Cascade Server 7.10 instance, with about 150 users.

Recently our DBA noticed that the table, CASCADE.CXML_MAIL, has grown to 75GB.

I logged in as administrator and cleared the 2,000 messages I had in my mailbox, but it barely cleared up 4GB.

Further looking into the table found that System messages to various users are taking up the space. So our power users each have about 1.5 GB of mail in the table.

There are some old users ( no longer active ) who also have a large volume of mail. I'm not able to log in as them, because our login is linked to LDAP, and these people don't work here any longer.

Is there anything I can do as the Administrator to clear up old messages, say older than 1 month ?

Do I have to email every user and ask them to each empty their mailbox ? How do I take care of the inactive users?

Thanks for your help,

Jeff

  1. 1 Posted by Ryan Griffith on 04 Nov, 2014 07:17 PM

    Ryan Griffith's Avatar

    Hi Jeff,

    The DB Optimization utility has a feature to remove read messages; however, this won't help much if the majority of the mail is unread. Currently, the best thing to do would be to simply pick a cut off date and remove all messages older than that date.

    For example, let's say you want to remove all messages older than 6 months ago (5/4/2014):

    delete from cxml_mail where receiver is not null and sendDate <= 1399161600000 order by parentId desc;
    

    Note: you need the order by here because broadcast messages actually have parent messages associated with them. Omitting this would result in constraint errors.

    Also, I wanted to add there is a related suggestion on our Idea Exchange to add more advanced options, including purging (un)read messages based on number or age.

    Please let me know if you have any questions.

    Thanks!

  2. 2 Posted by jperreault on 05 Nov, 2014 07:34 PM

    jperreault's Avatar

    Hi Ryan,

    Thanks for the info. I tried the SQL statement you gave, but oracle doesn't like the ORDER BY clause in the statement.

    Just want to double check - Should I delete the messages with null parent first ? Then messages with parentIds ?

    Thanks,

    Jeff

  3. 3 Posted by Ryan Griffith on 05 Nov, 2014 07:45 PM

    Ryan Griffith's Avatar

    Ah, my apologies Jeff, I had assumed you were running MySQL.

    Just want to double check - Should I delete the messages with null parent first ? Then messages with parentIds ?

    Actually, I believe you would want the reverse. That is, you would want messages with a parentId that is not null first. I'm guessing you are thinking about splitting the delete into two statements, which should definitely work as long as you have the correct order (to avoid constraint errors).

    Please let me know if you have any questions.

    Thanks!

  4. Ryan Griffith closed this discussion on 14 Nov, 2014 09:37 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac