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
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
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
timestamp type stores a database-relative time that is computed by incrementing an 8-byte binary counter on each
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
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.
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).