Sunday, September 21, 2014

Taxonomy Update Scheduler not working ?

Let's try to understand why the Taxonomy Update Scheduler job stops working.

Taxonomy Update Scheduler Job (UpdateHiddenListJobDefinition)
This update job is responsible to update the terms in a hidden list which is located in each site collection.
The reason for this is speed, it's faster to query a internal list than the managed metadata service.
But of course this complicates things, keeping things synced are always a challenge.

The job is executed every hour, so if you rename a term, it can take one hour before the changes
are updated in your site collections.

There is one update job per web application.

You can find the list here:
[site collection url]/Lists/TaxonomyHiddenList/AllItems.aspx

It's a standard list which contains one row per term.
One term can look like this:
Hidden list item

Search uses this hidden list too, the fields named CatchAllData and CatchAllDataLabel.
So if the search results are wrong, then the hidden list aren't up to date because of trouble with the Taxonomy Update Scheduler job.

Anyway i had this term that did not update in my site collection no matter what i did, so i had to find out why.

If you Google this problem you probably end up with this script:

$url = "https://yoursite"
$site=Get-SPSite $url

And of course running this script definitly works.
If you rename a term in the termstore and run this script, the taxonomy hidden list is updated.
But the update job still doesn't work after running this script..
This method is very cpu intensive and updates all terms in the hidden list by querying the termstore for every term.

Well we can't really manually run this update every time we make changes to terms.

I even saw someone that had created a custom timer job, calling this sync method instead of using the builtin Taxonomy Update Scheduler job, this is not recomended at all. It's a resource hog.
The update job only updates terms that has changed.

So let's try to find out why it isn't working:

I first checked the ULS after executing the update job and saw this:
  • Entering monitored scope (Timer Job UpdateHiddenListJobDefinition). Parent NoName=Timer Job UpdateHiddenListJobDefinition
  • Site Enumeration Stack:    at Microsoft.SharePoint.Administration.SPSiteCollection.get_Item(Int32 index) ..snip
  • Site Enumeration Stack:    at Microsoft.SharePoint.Administration.SPSiteCollection.get_Count() ..snip
  • Site Enumeration Stack:    at Microsoft.SharePoint.Taxonomy.UpdateHiddenListJobDefinition.Execute(Guid targetInstanceId) ..snip
  • Updating SPPersistedObject PersistedChangeToken Name=086eb3a5-8529-4a57-a9f7-3171294aa483TimeOfLastTaxonomyUpdate5d8ae25a-b6b3-470c-a0a2-513260cc8ce5. Version: 1453655 Ensure: False, HashCode: 20286682, Id: f2c91ae7-27fb-43e0-9d25-217a6dca71ff,    Stack:    at Microsoft.SharePoint.Taxonomy.UpdateHiddenListJobDefinition.SetChangeToken(String databaseKey, PartitionSettings proxySettings, String value) ..snip
  • Leaving Monitored Scope (Timer Job UpdateHiddenListJobDefinition). Execution Time=69.0284659083766
(WCF  calls stripped)

So after wasting several hours pursuing these errors, well let me tell you this: this is NORMAL.
Pfft another exampel of Microsoft polluting the ULS with crap to keep the consultants world wide busy.

I tried a lot of things to get this working, checking rights etc. but nothing worked on the problem term.
Then I decided to create a new term, and use that in a site collection. It appeared in the taxonomy hidden list. I renamed it in the term store, executed the update job. And voila the term was updated ??
So new terms works, "old" terms does not work..

So i started looking at the timer job to get an idea what it was doing:

There is a method beeing called named: GetChangesForListSync
which in turn Calls this stored proc: proc_ECM_GetChangesForListSync
in the Managed Meta Data database.

This proc loads data mainly from a table called ECMChangeLog.
Every time you change a term, it will be recorded in this table.
I checked this table and both my working and non working term was logged to this table as changed.

Then it joins on a table called ECMUsedTerms.
So here i found out that my working term had an entry in this table, and the non working term was missing an entry in this table.
Great, finally getting somewhere!

Ok so i started looking at the procs for this table: proc_ECM_AddUsedTerm
This is used in TaxomomyField.AddTaxonomyGuidToWssCore

So when you look at the TaxomomyField.ValidateTaxonomyFieldValue function which in the end calls the function TaxomomyField.AddTaxonomyGuidToWssCore.
This function will only be called if the term is new, if it exists in the hidden list on the site it will use the wssid of that term and the ECMUsedTerm table is never updated.

Ok, looking some more at the stored procedures for this ECMUsedTerm table, i found this:

This proc is called from TermStore.UpdateUsedTermsOnSite(SPSite site)
Okay... i have never seen or heard about this function at all :)

Anyway i made this script and executed it:

$url = "https://yoursite"
$site=Get-SPSite $url
$tax = Get-SPTaxonomySession -Site $url
$ts = $tax.TermStores[0]

Make sure you are a sitecollection admin when executing this script.
There is a check for this in the function.

And if i now looked at the ECMUsedTerm table it had way more rows than before, and my problematic term finally had an entry in the table.
Now renaming my problem term was updating just fine by the taxomomy update scheduler job.

Ok so what has happened here ??

There are at least two scenarios that can cause this:

Scenario 1:
If you recreate the Managed Metadata Service with a clean database, importing the terms with the same id's then of course the ECMUsedTerm table is empty / information is lost.
Your solution will still work using taxonomy pickers etc. and you will probably never notice anything unless you change a term.
(This was the reason for my troubles, i had to recreate the MMS because of the bug in SharePoint 2013 which sometimes makes the MMS corrupt).

Scenario 2:
If you move the content database from one farm to another (maybe from staging to prod) which of course has a new Managed Metadata Service, then the ECMUsedTerm table is of course not in sync.

I'm not sure what happens if you do a site restore, if this takes care of this sync.. But i have my doubts.

And of course there are probably several other reasons for the hidden list's getting out of sync with the termstore

Anyway lesson learned:
If the terms are not updating when executing the Taxonomy Update Scheduler job then execute UpdateUsedTermsOnSite for every site collection.


  1. This comment has been removed by the author.

  2. Outstanding detective work. I am going to post links of your post everywhere so people can come here and get the skinny on one of the most annoying bugs in SharePoint 2013 Managed Metadata timer jobs not updating. Well done! Hopefully Microsoft will pick up on this and put it into a hotfix. Amazing that in 2+ years they have not found and fixed this on their own.

  3. Well, I think I might have sung your praises a little too soon hehe I see exactly what you're talking about with the fields missing but the UpdateUsedTermsOnSite did not add them back for me. :(

  4. Make sure the user which the powershell is executed under is a sitecollection admin on your rootsite.
    If not you will get a unauthorized exception in the uls.
    What does the ULS say when you execute the script ?

    1. Yes, I am in the Site Collection Administrators and am also a farm admin. I ran the commands in both an elevated Powershell window (as admin) and just as me. It didn't change anyhting. ULS just says it is running the command: "Ensuring fields on hidden list of site http://sharepoint/projects". That's the only output. Nothing new appears in the ECMUsedTerms1 table.

      I wonder if I need to run that more expensive script you referred to, at least one time?

    2. Sorry, the table is just called ECMUsedTerms. Not sure how a 1 got in there....fat fingers, I guess.

    3. Humm well you can try to run the synchidden list, i actualy think i tried that first. This will not update the ECMusedTerms table.
      But it will update the terms in the hidden list. Might do some additional cleanup..

      Verify that the terms are correct.
      Rename a term.

      Then rerun the UpdateUsedTerms and see if it updates the table, and finally run the taxonomy update scheduler job.
      The term should be renamed.

      It should work.
      I have used it in 2 farms now :)

  5. I think it may actually be working. The problem I was really aiming to fix is this: On pinned terms, if you change the Navigation Node Type to Simple Link Or Header and you leave the field blank, SharePoint is supposed to make the node into a simple label, not a navigation node. This property doesn't seem to be "inheriting".

    It is not propagating to the sites that have pinned it. In other words, my root level terms like "Corporate" and "Department" are rendered as a link to /Corporate and /Department. The only way to get the elements to behave properly is to manually change each pinned term location and clear the Simple Link or Header. :(

    1. I just wanted to close this out by relaying what I have learned. The URL field of a pinned term is NOT updated when the original (parent/master) term is changed. Other aspects of a pinned term can propagate to the subscribers but NOT the URL. Seems ignorant for Microsoft to have made it this way but I'm sure I am overlooking some obvious reason why they did. Anyway, I plan on writing a little timer job that will look every few minutes and update the subscribers' URLs.

    2. Thanks for sharing. I haven't used pinned terms before.
      But i'm pretty sure Microsoft will call it a feature not a bug ;)

  6. thanks for sharing, it worked like charm for me.

  7. I appreciate the time you took to share this. It really helped me out. In my case I ran the SyncHiddenList Script for an immediate fix, followed by the UpdateUsedTermsOnSite script to ensure that it does not happen again.


  8. You the man!
    I had moved my term store from dev to test to prod via db restore and was suffering from this affliction.
    In my case I did have to run the full powershell sync and then run update to the site collection and then it started working.
    Thanks so much!

  9. I'm experiencing this problem on SPO, which means I'm SOL :(

  10. I must say thank you for this great finding and solution!

    Thank you again!