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:
where [Stamp] <= (
select Cast(CONVERT(nvarchar(100), [Value]) as int) from [Properties] where [Key] = 'EQSTAMP_WEBSERVERINSTANCENAME'
order by [Created] desc
<=. 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
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.