Mfcttrf

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

However have 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 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 have 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.

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

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