Sitecore 7.2 Not rebuilding Lucene Index

Question

We're using Sitecore 7.2 Update 4, and have 6 Lucene indexes across our sites in a single Sitecore instance.

In the production environment, but not locally or on staging, the web index is not rebuilt when an item is published and we need to rebuild the index manually. Both staging and production use separate content authoring and delivery servers with the web index rebuild on publish being initiated on Staging but not in Production. Apparently there has been a recent backup of databases from Prod to Staging so there shouldnt be significant differences there.

Indexing Strategy on CD: SwitchOnRebuildLuceneIndex

    <strategies hint="list:AddStrategy">
          <strategy ref="contentSearch/indexUpdateStrategies/onPublishEndAsync"/>
          <strategy ref="contentSearch/indexUpdateStrategies/rebuildAfterFullPublish"/>
          <strategy ref="contentSearch/indexUpdateStrategies/remoteRebuild"/>
        </strategies>

And on CM:

<strategies hint="list:AddStrategy">
     <strategy ref="contentSearch/indexUpdateStrategies/syncMaster" />
</strategies>

Errors in Sitecore logs: none

All the conifuration settings in ScalabilitySettings.config exist in the web.config. Although InstanceNames are blank, they are recorded in the EventQueue table successfully using the default format of MachineName-IISSiteName. EnableEventQueues is set to true in web.config.

Im seeing ItemSavedRemote events in the EventQueue in the web db but IndexingStartedEvent is never recorded in the core db.. That seems to be the problem but not sure why the IndexingStartedEvent is not fired.

We have had someone out from Sitecore and they have told us the configs look good. Any suggestions as what may be causing the issue?

UPDATE 3/4/17 - Apparently this has started working again in Prod. When an article is published, the index is rebuilt and display on the website. To me, this is still an open issue as we dont know what caused this issue and it may break again - it has started working again once before and then stopped after a couple of months with no code/config changes. Suggestions as to what that might tell us?

Answer 1

The EventQueue - the medium of communication between your Sitecore instances - is timestamp-based, which can lead to issues if you are using replication and something happened that caused your SQL instance to re-initialize/failover or if the EventQueue's last processed timestamp becomes corrupted.

In order to ensure that this is not the issue, connect to each of your databases (you could do this just for Web, but if it affects one it likely affects all of them) and execute the following SQL commands (order matters; see below for details):

DELETE FROM [EventQueue]; --optionally, you can change this command to delete older than a certain date instead

DELETE FROM [Properties] WHERE [Key] LIKE 'EQStamp_%';

What did I just do?

Cleared the EventQueues

It is safe to clear the EventQueues for your databases, and often times this will result in a performance boost - sometimes noticeable; other times not. When these tables become too large, Sitecore often has trouble processing them, hence why Sitecore includes Cleanup Agents for the EventQueues to delete old records.

Basically, all you are doing here is resetting the stage and giving your environment a "clean slate" as far as events queuing is concerned.

Side-note about the Cleanup Agents: since the agents run on a relatively long default interval, the EventsQueue can get very large on a bigger site. Additionally, since agents can be troublesome when longer intervals are used (reference the section "The Problem with Agents" in this post), I typically create Windows Scheduled tasks to regularly clean out the EventQueues for larger sites, due to the greater reliability of the scheduling.

Cleared the Reported Times the EventQueues were Last Processed

Sitecore stores the value that represents the last time a database's EventQueue was processed in the Properties table. The Key for this value is EQStamp_<MachineName>-<IISAppPoolName>.

By deleting all values from the Properties table with a Key that starts with the value 'EQStamp_' (via the % wildcard and the LIKE operator), you are essentially "resetting" the last recorded time the EventQueues were processed to "never."

As such, the next time Sitecore goes to process the EventQueue for that database it will see any records in the EventQueue table as new events, and will thus begin to process them. As such, it is important that you clear your EventQueues before doing this step. If you do not, your instances will see all of the records in the EventQueue table, including those already and not yet processed - as new events and will begin to process them all. If you have a large amount of records in your EventQueue this can take a very long time, so be sure that your EventQueue is cleared before you clear the last processed timestamp.

Why did I do that thing I just did?

Database-Relative Time and the Last Processed Time

The EventQueue uses the recorded last processed time for determining which events are new, so as to retrieve them for processing. In order to retrieve new events for processing, Sitecore requests all events from the EventQueue that are newer than the last-processed event, as determined by comparing the timestamp values.

The way that the EventQueue's last processed time is recorded is by creating a a copy of the [EventQueue].[Stamp] column value of the last processed event. The [EventQueue].[Stamp] column is of the timestamp type (deprecated since SQL 2008), and its value is copied to the Properties table as a string (stored in an ntext type column). The newly created record in the Properties table is the one that we cleared out, with the Key column value of EQStamp_<MachineName>-<IISAppPoolName>.

The timestamp type stores a database-relative time that is computed by incrementing an 8-byte binary counter on each INSERT or UPDATE operation. According to MSDN:

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock

Essentially, what this means is that when the SQL instance re-initializes it's basically starting out with a fresh database-relative timestamp, meaning that any newly generated timestamp values will be starting from 0. More accurately, the timestamp values will be starting from the point at which the replication started.

Here's where this gets to be a problem: assume that when the replication started, the last processed timestamp was 601283 (which is a number I took from a random web database's Properties table in my local SQL instance). Time passes and the last processed timestamp of the SQL instance being used, SQL A, grows to 1301283. At that point, something happens and SQL A is replaced with the replication set, SQL B. The problem is that SQL B's timestamp value is still 601283 (the value from immediately before replication started), but because SQL B was a replication set its Properties table has the last processed time recorded as 1301283 (the value the timestamp on SQL A grew to, before SQL B took over). As a result, new events are created in SQL B with timestamp values 601288, 601313, 601337, ... and so on (note that these are just examples) meanwhile Sitecore is only requesting events with a timestamp value greater than 1301283. What this means is that the database now thinks that the new events have already been processed, because compared to the recorded last processed timestamp, 1301283, the new events' timestamp values, e.g. 601288, are in the past! This means that new events are effectively ignored by Sitecore.

It should be noted that left unfixed the timestamp value should eventually catch up to the last processed value stored in the Properties table. The amount of time this takes depends on how long your replication has been running. For some instances, this issue might not be that big of a problem, e.g. if your replication has only been running for a few hours, a few days, or sometimes even longer, depending on how heavily the instance is being used and changes are being made. However, if your replication has been running long enough then it could take weeks, months or even years for the timestamp value to catch up.

To sum it all up, this problem can be remedied by simply deleting the EventQueue's recorded last processed time from the Properties table, so as to reset the value that Sitecore assumes a "new" event's Stamp column value must be greater than.

Corruption of the Last Processed Time

While not super likely, it is also possible for the EventQueue's recorded last processed time to become corrupted. This can happen as a result of changes in SQL infrastructure, backups/restores, etc. If any of these scenarios may be relevant, this solution is very low effort and low risk. In general, it pays to execute the queries, specified above, to be safe and eliminate these issues as a possibility.

Credit

Much of the credit for the explanation of why this solution works is due to Per Manniche Bering and his post, Sitecore CD nodes not picking up events after replication re-initialization. I must also give him credit for finding and highlighting (which I matched via emboldening) the excerpt from MSDN that I quoted above, which I found to be of great help in fully understanding the issue (ergo, why I repeated it, here).

Answer 2

In Sitecore.config (for failing environments), locate this setting.

<eventQueue>
  <!-- Time between checking the queue for newly queued events. If new events are found they will be raised. -->
  <processingInterval>00:00:02</processingInterval>
</eventQueue>

Try and increase the processingInterval. Suspicion being, you may be running into a deadlock type of situation. So for example, set it to this:

<eventQueue>
  <!-- Time between checking the queue for newly queued events. If new events are found they will be raised. -->
  <processingInterval>00:00:30</processingInterval>
</eventQueue>

Reference: Index are not updating after db restore

Answer 3

Since you have CM and CD Servers, it is required that you enable the scalability settings. This is because it allows the servers to know when the instance specified in the scalability settings is inserted into the event queue, it should triggers the event.

Moreover, some points to note:

1. Make sure in the properties table, there a re no records of instances that are not CM and CD. For example, instance for Staging is also present in the Properties table of your CM or CD.

2. Make sure that you don't have verbose logging enable on the servers.

3. Indexes normally get rebuild once all the servers are in sync. This may be a reason why you need to enable the scalability settings.

4. Check if indexing events are populated also in the core db of the CD server. If not, it may be that the core db replication is not working accordingly

Answer 4

I'd hope someone from Sitecore would have checked this, but just in case: Have you looked at your event queue configuration?

It's a while since I worried about stuff like this, but if I remember correctly, triggering an index operation for Lucene runs it locally (on your CM box) and sends out an event to inform all the CD boxes that "an index build is required now". Different rebuild strategies use different events to suit their approach.

Off the top of my head, I can think of a few things that might be wrong here that could cause issues with indexing:

• Remote events might not be enabled.
  (In the "scalability settings" configuration file I think)
• The instance names of your servers are odd, so that they don't realise the event being broadcast is for them.
• Something in the CD server config has detached the index rebuild strategy from the appropriate events, so they fail to respond to the event.

You mentioned using "SynchronousStrategy" - based on the docs, I believe that is making use of the events for item-level actions like "ItemSavedRemote". You might want to check that is triggering on your CD servers?