13

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.

17

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.

enter image description here 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.

enter image description here

  1. 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.

enter image description here

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

  1. 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

enter image description here

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.

  • 1
    Thanks for all your help with this Hishaam. Just to clarify on the above in case anyone is unsure I've confirmed on Slack with Hishaam that: According to the code when the event queue cleanup task runs then if an item in the event queue: a) has not yet run OR b) is in progress but is not completed. Then it will not be removed from the event queue table until it has run and has completed. – Adam Seabridge 2 days ago
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.