Forums (Obsolete)

Old Forums

Installation and deployment Questions on installation, system configuration and deployment to the live server.

Version 5.x > Installation and deployment > Table CMS_AttachemtnHistory is quite large

View modes:

Member

lancetek - 7/11/2011 3:38:42 PM

Table CMS_AttachemtnHistory is quite large

Hi,

I've just noticed that my CMS_AttachementHistory table is huge. I'm sure the culprit is the AttachmentBinary field, as it seems to be filled with binary data. In Site Manager > Settings > files
I've had the 'store files in the database' unticked since I first started this site, so I'm wondering if this is a bug.

ie - I'm assuming that binary file data should NOT be stored in the database.

Cheers,
Lance

Kentico Support

kentico_radekm - 7/11/2011 7:05:26 PM

RE:Table CMS_AttachemtnHistory is quite large

Hello.

When you delete some document with binary data (attachment, e.g. cms.file), the record is created in CMS_AttachmentHistory table. The content of this table are deleted attachments, so the files which are used no longer. You can delete content of this table. This table should be deleted also when you go to CMSDesk -> My desk -> Recycle bin and click Empty recycle bin.

Best Regards,
Radek Macalik


Member	lancetek - 7/12/2011 2:38:26 AM RE:Table CMS_AttachemtnHistory is quite large I've just recycled like you said... it said deleting for a few seconds and now it's completed... and the table is still 340MB. Can I safely truncate it? Lance


Kentico Developer	kentico_ivanat - 7/12/2011 6:40:51 AM RE:Table CMS_AttachemtnHistory is quite large Hi, yes, you can delete the content of the table, but I would like to suggest you to create a database backup before doing any change in default DB, even if scenario you have described should be with no problems. Best regards, Ivana Tomanickova


Certified Developer 8	sparker-pmc - 1/7/2013 2:14:07 AM RE:Table CMS_AttachemtnHistory is quite large Hi Kentico, Is this still accurate in v6 and 7? CMS_AttachmentHistory is 1.2 GB for us and that is after i have deleted all of the recycle bin. Is this table also storing the past versions of any documents with Binary data?

Kentico Customer Success

kentico_martind2 - 1/8/2013 2:59:58 AM

RE:Table CMS_AttachemtnHistory is quite large

Hi,

Yes, it's still working the same way in v6 and v7. It's growing in a size especially if you are using versioning... You can delete the version history (or set the version history to a smaller number) find the example code in the Developers Guide: http://devnet.kentico.com/docs/6_0/devguide/index.html?workflow_advanced_api_examples_versioning.htm

The version history length is applied just to a new attachments, not for those which are already stored in the database. But it's good to set the limit lower to protect it in the future.

However I was solving this issue a few weeks ago with another customer and the solution could be also really simple just to get the size down... because the most space is taken by a binary data of the old attachments. You should be able just to clear that data by SQL command like this...

UPDATE CMS_AttachmentHistory SET AttachmentBinary = NULL WHERE ... your condition to fit your needs (e.g. AttachmentLastModified < DATEADD(month, -1, GetDate()) ... for all attachments older than 1 month)

I hope this will help you.

Best regards,
Martin Danko

Certified Developer 8

sparker-pmc - 3/6/2013 6:29:38 PM

RE:Table CMS_AttachemtnHistory is quite large

Hi All,

this did not seem to work as it would cause the files on the drive to be deleted as well.

Example:

- Create a file document type with the attached image. this will add a record to CMS_AttachmentHistory with the data in the field AttachmentBinary (at least this is the case with versioning)
- Set AttachmentBinary to null for this new image.
- If a user clicks save on the document type it will delete the attachment file on the disk.


Kentico Customer Success	kentico_martind2 - 3/14/2013 8:29:54 AM RE:Table CMS_AttachemtnHistory is quite large Hi, Yes, this script is suppose to delete the attachments, depends also on your settings if you are storing files in the Database or not. Via this SQL script you should delete only the old attachments. I'm not pretty sure what behaviour were you expecting? Best regards, Martin Danko

Top