When is it safe to remove an `EventQueue` record?

Question

Sitecore stores its events in a database table called EventQueue. It is used for centralized event-based communication between Sitecore instances.

Sometimes this table grows quite large and it needs to be cleared of old events. Of course, there's a CleanupEventQueue task in Sitecore that you can schedule to run periodically. But sometimes one may need to clean that table manually.

My question is, when is it safe to remove an EventQueue record from the table? Or in other words, how can I know that a remote event has been fully processed by all instances?

Answer 1

After having processed an event from the queue each instance updates the associated last processing timestamp (LPT) which is stored in the Properties table and has the key in the following format 'EQStamp_{Sitecore-Instance-Name}'

Therefore, you can use the LPT to filter out events which have already been processed by a given instance - literally just the opposite to what the method GetQueuedEvents(string targetInstanceName) of Sitecore.Eventing.EventQueue does.

There is a Stamp column associated with each event entry in the EventQueue table. The value in Stampis generated automatically by SQL Server (see http://msdn.microsoft.com/en-us/library/ms182776.aspx) when the event is added to the table. The Stamp value of a particular event eventually gets saved as the last processing timestamp for the instance once it's been processed. So if EQStamp_{Sitecore-Instance-Name} equals to 1666508, you can use the following SQL query to retrieve events which have already been processed:

SELECT * FROM EventQueue WHERE Stamp < 1666508 

Answer 2

In order to be 100% sure that a particular event was processed by all servers, you would need to write a rather complex query.

To illustrate, I've written a query to return all events processed by a single web server:

SELECT *
  FROM [EventQueue]
  where [Stamp] <= (
    select Cast(CONVERT(nvarchar(100), [Value]) as int) from [Properties] where [Key] = 'EQSTAMP_WEBSERVERINSTANCENAME'
  )
  order by [Created] desc

Note the <=. If an event was processed by a web server, the [Stamp] value stored in the [Properties] table will be equal to that event, therefore this query gives the most up-to-date processed events.

Since an event can be processed by any number of connecting web servers, there is no guarantee from this query that all web servers have processed these same events. Therefore, do not perform a delete using this query.

To get the full list of processed events would require some SQL magic. You would need to pass in the full set of web server InstanceNames in a cluster and do a join of the results of the query above, but with all InstanceNames. Even then, it would not be 100% accurate for a number of reasons. There are many legitimate reasons for the [Properties] table to show a different EQSTAMP Value for different InstanceNames:

• Different App Start times
  • The EventProvider is initialized on app start. By default, it begins polling the EventQueue every 2 seconds. It is quite likely that various CD servers, for example, were started up at different times, therefore their polling times will differ. If the 2 second queue time is increased to 1 min+ the difference would be even more noticeable.
• Different Event Types
  • Events can be set to runGlobally or runLocally. This determines if a server is supposed to execute an event. In normal operation, it would be entirely legitimate for a CM instance name to have a different value in the EQSTAMP field than two related CD servers simply because they are responsible for executing different events.
• Old Instance Names
  • Whether it is caused by migrating databases or messing with the ScalabilitySettings.config > InstanceName- the [Properties] table keeps a record of every web server that has ever connected to it. This can cause you to receive more results than expected if you run a query such as: SELECT * FROM [Properties] where [Key] like 'EQSTAMP%'

In my opinion, there are simply too many variables to consider when removing events, which is why Sitecore chooses to remove old events based on a time interval.

Maintenance Window Tip

The time interval for Event Queue clean up should be considered during any maintenance window. When a web server is taken offline, obviously all processing of events ceases. As soon as it is turned back online and the EventProvider is initialized, it will resume processing events from where it last ended based on it's EQSTAMP value. It will process events from bottom to top- this can lead to very long initialization times in Sitecore as it churns through all new events.

Therefore, if you had an aggressive EventQueue cleanup interval, of say 1 hour, then you could not turn a server off for longer than 1 hour. If a web server did go offline for longer than an hour, then you could not be confident that it is in the same state as a server that remained online the entire time.

Answer 3

For reference there is a nice response on StackOverflow as to how cleanup the queue.

Sitecore recommends fewer than 1000 items in the table and the solution to keep them below that is to run the agents more frequently. Assuming the server times are synchronized and properly configured, you should expect within a few minutes of the agent running that the data is safe to delete.

Note: The default scheduled frequency to run is every 5 minutes and the interval for each agent is 4 hours.