Forums (Obsolete)

Member

a.mueller-livingtech - 7/14/2011 11:52:56 AM

DB-Table-Size 'CMS_Email'

Hi

We are running a v.4.0-installation of Kentico and the database-size is growing constantly. Currently we reached about 420MB for quite a small website (Images/Files are stored in FS, not in the DB).

Some research showed that the table "CMS_Email" uses approx. 320MB of the current DB-Size of 420MB.

Now, the client is sending two newsletters a week to over 2'000 recipients. A quick look into the table shows me, that kentico is saving all data for each recipient and each mail (including the whole content). I get the point of doing this for resuming mailings. But is there some built-in way to clean up this data? Or do I have to do this by sql?

Thanks for any help and answer!

Andy

Kentico Consulting

kentico_borisp - 7/15/2011 3:20:08 AM

RE:DB-Table-Size 'CMS_Email'

Hello,

Thank you for letting us know about this issue. Regrettably, this was a bug in the previous versions. I'm very sorry for this inconvenience. It was fixed in the 5.5 version (hotfix 5.5.4):

Error: Optimized deleting of archived e-mails prevents "Out of memory" exceptions.
The Clean e-mail queue task isn't executed correctly if there is a great amount of emails already in the database.

To fix the issue I would suggest you to upgrade your system and apply the hotfix.
If you don't want to upgrade, you can the delete emails from the database (CMS_Email table) manually. The Clean e-mail queue scheduled task should delete the emails automatically, if the queue is shorter.

You can also write a custom scheduled task to clean it manually.

Best regards,
Boris Pocatko

Member

LivingAndy - 7/15/2011 11:31:03 AM

RE:DB-Table-Size 'CMS_Email'

Hi Boris

Thanks for your answer.
Our client didn't renew the license, so an upgrade is no option. We have to stick with the current installation.

Concerning the bug: our client bought a license of Kentico. When you say that the licensed version had a bug, then I guess it would be your task to fix this bug and to provide the fix for us, wouldn't it? I mean, if I'd sell you a car with brakes not working, then you wouldn't accept me telling you to buy an upgrade for the same price as the car itself to get the brakes fixed, not?

So, would it be possible for you to write a custom scheduled task that would clean the mail-table? I guess this would be the easiest way for all of us (except, maybe, you).

Best Regards,
Andy

Kentico Consulting

kentico_borisp - 7/16/2011 5:04:08 AM

RE:DB-Table-Size 'CMS_Email'

Hello,

Regrettably, this doesn't correspond with our bug fixing policy. Our policy says that we deliver bug fixes within 7 days on the current version and not on the previous ones. You are free to install the current version and check all the tasks available there, however writing custom workarounds for old versions isn't possible in this case.

So lets compare software and cars. If you buy a new car you get only free repairs within a given period of time or as per bought license. After that you need to service your care for your own money.

I think you agree with me on this.

In addition, your client knew that hot fixes are only delivered to the new version and that support is provided only after paying the maintenance fee.

I also provided you with the workaround to solve this issue, so you are able to create a workaround on your own.

Best regards,
Boris Pocatko

Member

LivingAndy - 7/18/2011 7:05:13 AM

RE:DB-Table-Size 'CMS_Email'

Hi Boris

Thanks for your reply. Of course you're right concerning the license and the fixing. I'm pretty sure your legal-department did a good job. So, although I'm not completely sattisfied with your policy (if you build a car with brakes not working, you have to fix this even after the service-period as the car has not been shipped in a proper working way as every customer would assume it to be - but I'm not completely sure that it's beeing handled like this :-)), I'll accept it, of course.

Anyway, maybe you could help me with this: which records can I delete from the table CMS_Email?

Best Regards,
LivingAndy

Kentico Consulting

kentico_borisp - 7/25/2011 3:12:58 AM

RE:DB-Table-Size 'CMS_Email'

Hello,

You can check the EmailStatus column and if there is a 3 the email can be deleted. Here are all the statuses which apply to this column:

/// <summary>
/// E-mail is being created
/// </summary>
Created = 0,

/// <summary>
/// New or failed e-mail waiting for sending
/// </summary>
Waiting = 1,

/// <summary>
/// E-mail is being sent
/// </summary>
Sending = 2,

/// <summary>
/// Archived e-mail
/// </summary>
Archived = 3

Best regards,
Boris Pocatko


Member	LivingAndy - 7/25/2011 3:38:01 AM RE:DB-Table-Size 'CMS_Email' Hi Boris Thanks a lot for this information. This should help me. But the strange thing is, that the state of all records in the table is '1'. But the mails have been sent (some of them older then a year and not in the CMS as not sent)... Does this make sense? Best Regards, Andreas

Kentico Consulting

kentico_borisp - 7/25/2011 5:31:55 AM

RE:DB-Table-Size 'CMS_Email'

Hello,

This seems to be the mentioned bug. In addition are you using any custom code during the e-mail sending?

I would suggest you to try the following approaches:

1) Enable the "Archive e-mails (days)" setting in CMSSiteManager / Settings / E-mails. If enabled, the email status should be set to 3 after it is sent. Please check if this works on a test email (also check the database table).

2) Check the field [EmailLastSendResult]. This field indicates, if there has been a problem with the sending out of this email. If a email has this set to NULL, the email should have been sent fine. You can try to send a testing email and check if it is added to the CMS_Email table and if the mentioned column is still null. If an email is older (e.g. 3 days old) and your testing email got delivered successfully, all the older emails should have been sent out also successfully and can be deleted manually from the table.

Best regards,
Boris Pocatko