How to remove obsolete/orphaned items from TaxonomyHiddenList

Question

Explanation of environment: I am running SharePoint 2010 SP1, December CU's. I have a list item which has a taxonomy column. As expected, if a term (e.g. “foo”) is deleted from the term store, users no longer see “foo” as an option for new items, but the term remains on previous items because “foo” is not deleted from the TaxonomyHiddenList by the Taxonomy Update Scheduler job.

Problem: This presents problems with search because if “foo” is deleted, we don’t want anyone to ever know “foo” existed anywhere on our site.

Desired Behavior: In addition to updating current items, I would like orphaned terms to be deleted from the TaxonomyHiddenList

Proposed Solution: Write a custom timer job to iterate through all terms and delete any orphaned items from the TaxonomyHiddenList

Question: Is this a viable solution? Am I missing something? Is there a more elegant way to handle this than manhandling the TaxonomyHiddenList?

Answer 1

I have successfully used the script from this blog: http://tjwheeler.blogspot.com/2010/11/content-migration-and-taxonomy-fields.html

Answer 2

This out-of-the-box timer job is maybe doing this job: Taxonomy Update Scheduler. Documentation at Microsoft TechNet

Answer 3

I don't believe anyone can recommend fiddling with hidden system lists. There probably won't be any concequences, but there's always a slight risk involved.

Personally if this would be a "customer" case, I would open a MS support ticket and tried to get an official comment on whether this kind of cleanup job would be supported. They might also suggest a better official alternative; I wouldn't be surprised if these orphaned TaxonomyHiddenList items would be deleted after some OOB job X has run Y times...

Answer 4

Well I had this trouble today... And fed up seeking a solution, I went to SQL server and deleted them in table !

In the table ECMTerm, find the ID of the term you want to delete, mine were 3209 to 3213. Then apply this :

Delete [MetadataMing2013].[dbo].[ECMTermSetMembership] where Termid in (3209,3210,3211,3212,3213)

go

delete [MetadataMing2013].[dbo].[ECMTerm] Where id in (3209,3210,3211,3212,3213)

go

Delete [MetadataMing2013].[dbo].[ECMTermLabel] where Termid in (3209,3210,3211,3212,3213)

go

delete [MetadataMing2013].[dbo].[ECMTermProperty]     where Termid in (3209,3210,3211,3212,3213)

go

delete [MetadataMing2013].[dbo].[ECMTermDescription]  where Termid in (3209,3210,3211,3212,3213)

go

delete [MetadataMing2013].[dbo].[ECMChangeLog] where objectid in (3209,3210,3211,3212,3213)

go