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: 
User avatar
Member
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

User avatar
Kentico Support
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

User avatar
Member
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

User avatar
Kentico Developer
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

User avatar
Certified Developer 8
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?

User avatar
Kentico Customer Success
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

User avatar
Certified Developer 8
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.

User avatar
Kentico Customer Success
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