How-to: Delete dangling files from a Telligent Community database

The other day I came across a bug when upgrading my site to Community Server The Community Server 2008 upgrade tool would throw an exception when trying to move my files into CFS. The exception was result of the upgrade tool trying to pull down post attachments where the Section or Post didn’t exist.

This might not seem like such a bad thing, but it is for two reasons:

1. You have a file that is taking up database space that isn’t accessible within Community Server.

2. Community Server appends the SectionID to file name. Since the SectionID is null an exception will be thrown when creating the file name. I created the following SQL statement that will delete all cs_PostAttachments where the SectionID and ApplicationType is NULL and the section doesn’t exist in the cs_sections table.

   DELETE
     cs_PostAttachments
   FROM
     cs_PostAttachments
   LEFT JOIN
     cs_Sections
   ON
     cs_Sections.SectionID = cs_PostAttachments.SectionID
   WHERE
     cs_PostAttachments.Content NOT LIKE 0x AND
     cs_Sections.SectionID IS NULL AND
     cs_Sections.ApplicationType IS NULL