Attachment Histories without Corresponding Attachments

Stephen Price asked on October 1, 2018 23:49

Hi,

I'm doing some cleaning of our Kentico instance and have come across an interesting situation. We seem to have about 3,400 AttachmentHistory records whose corresponding Attachment records (found via the AttachmentGUID column) don't seem to be present. Has anyone ever encountered this, and would it be safe to delete them?

I Found these records by running:

SELECT [AttachmentHistoryID]
  ,[AttachmentName]
  ,[AttachmentExtension]
  ,[AttachmentSize]
  ,[AttachmentMimeType]
  ,[AttachmentImageWidth]
  ,[AttachmentImageHeight]
  ,[AttachmentDocumentID]
  ,[AttachmentGUID]
  ,[AttachmentIsUnsorted]
  ,[AttachmentOrder]
  ,[AttachmentGroupGUID]
  ,[AttachmentHash]
  ,[AttachmentTitle]
  ,[AttachmentDescription]
  ,[AttachmentCustomData]
  ,[AttachmentLastModified]
  ,[AttachmentHistoryGUID]
  ,[AttachmentSiteID]
  ,[AttachmentSearchContent]
FROM [STAGING-Kentico].[dbo].[CMS_AttachmentHistory]
WHERE AttachmentGUID NOT IN (SELECT AttachmentGUID FROM CMS_Attachment)

kentico

Correct Answer

David te Kloese answered on October 2, 2018 13:57

Well it can still be used when connected to a previous version of a page.

you can check the "attachmentLastModified" column for when it was last changed (a remove or new version is also a change).

1 votesVote for this answer Unmark Correct answer

Recent Answers

Brenden Kehren answered on October 2, 2018 14:21

This is the biggest issue we have when performing upgrades. It's not just attachment version history either, it's pages and objects too. When cleaning that data up be very careful which records you remove because it will cause problems, especially if you're using workflow and pages or objects are checked out.

1 votesVote for this answer Mark as a Correct answer

Stephen Price answered on October 2, 2018 19:02

Thanks, y'all. We're battling an absolutely huge and overgrown production database that can't fit on any of our development machines right now. The culprit is that long ago "Store attachments in database" got checked and now years later we have gobs of files in there and it's of course hideously inefficient to store binary blobs in relational databases.

To battle it, I've built a "Kentico Spring Cleaning" module. One of the things it does is moves attachment binaries out of the db and into the file system. But I'm also playing with a system that can truncate attachment histories on attachments that haven't been touched in long time.

The repo for that module is here. It's built for Kentico 8.2 right now (we're furiously trying to upgrade, but testing is, um, hard when you can't fit the database into any of your developer machines).

Anyway, the feature I'm working on right now is to safely clear out old unneeded attachment histories. The idea is you can select attachments that haven't been touched in the last X days and remove all but the newest N attachment histories (but NEVER delete the last one, made that mistake already). Not exactly related to my question above, but it's how I came across the "orphan" attachment histories.

0 votesVote for this answer Mark as a Correct answer