How does Sitecore know an event in the event queue table has been processed?

Question

We are currently looking to optimize how often the event queue table is cleaned up and there are some really good posts on this such as this and this.

However, I have some concerns about cleaning up the event queue table too often and not allowing time for all the events to be processed. When we do large publishing or indexing jobs they can take 12+ hours to complete.

As I understand it when Sitecore processes the events it does not remove them from the table (hence the need for the event queue cleanup task).

However, what I can't see in the table is a status column of any kind. I can see the status property on the json for some events (in the instance data column) but not all of them. So, how does Sitecore know if an event has been processed?

Ideally, we would only cleanup events that have been processed and leave the other events that have yet to be processed in the table.

Answer 1

In order to know which if the events have already been processed, you will need to check the Properties table.

Using Sitecore OOTB

You need to see the http://[domain]/sitecore/admin/EventQueueStats.aspx page. This page allows you to see if there are still events being processed.

If we take the above as an example, it means that all events have been processed because the Records to be processed is 0. If there was still events to be processed, the value will increase based on the number of remaining events to process.

How Sitecore does this?

Sitecore makes use of the Properties table. After all the events have been processed, Sitecore updates (insert if not exist) an entry in the table.

• Sitecore versions up to 8.X, there is an entry which starts with EQSTAMP_***** in the Key column.

• For Sitecore 9.X, it is [targetDatabase]_EQSTAMP_*****.

This is what tells Sitecore if the events have been processed. There is a conversion which is performed for Sitecore to know if the events have been processed.

How it is done

1. Copy the value of the EQSTAMP. Below is a screenshot of my Properties table on the Web Database. So, in our case, the value will be 66999.

2. You need to convert the decimal 66999 to Hex. You can do this via a calculator. On windows, open the calculator and set it to programmer mode. Once done, select the DEC option and paste the value.

As you can see from the calculator screenshot, the value of the HEX is 105B7.

3. Execute the query select top 100 * from [EventQueue] with (nolock) order by [Stamp] desc on the target database, in our case, web database. The first entry should match the STAMP value of the HEX

If the HEX value matches the STAMP value of the first entry, this means that all the events have been processed.

You can read more on my article Sitecore Server Synchronization

UPDATE

I've looked a bit on the code that performs the cleanup of the event queue. So, as far as I've understood, if you have set the interval to keeps 6hrs but you have a job that is still running, the event will not get removed.

The reason is because, since that particular event has not been processed completely, Sitecore will not update the Properties table. So, the timestamp will still in the past.

Let say in the Properties table, the timestamp is 10th of October and you have an IntervalToKeep of 6hrs Now, you have 2 events in the in queue on the 11th of October:

• Event A is still being processed for more than 6hrs
• Event B has not been processed yet

When the agent runs, it will not remove Event A and B because the Properties table still has the entry of the 10th of October.

Answer 2

Expanding from the above answer you can try the below SQL Query to know the Number of Records to be processed from a given time and date:

SELECT COUNT(*) 
FROM EventQueue WITH (NOLOCK) 
WHERE 
  (InstanceName <> @p1 AND RaiseGlobally=1 OR InstanceName = @p1 AND RaiseLocally=1) 
  AND Stamp >= @p2
  AND Created >= @p3

where

• @p1 = InstanceName similar to databaseName-sitecoreInstance.local
• @p2 = lastProcessedTimestamp
• @p3 = fromDate