Cleaning up Sitecore Publish and Event queue tables to handle memory problems

As you all know that Sitecore Recommends that the no of rows in History, EventQueue and PublishQueue tables be less than 1000 records, this would help to prevent any timeouts while the cleanup agent runs.

Another setting we have for Publish/EventQueue agents is how long to keep the data and the default value is set to 30 days.

But when we have frequent item sync operations from third party systems or within Sitecore (followed with publish)- this would make Publish and Event queue grow which impacts cleanup tasks we have by default (to cleanup publish and event queue table) and memory spiking issues.
In such cases we should consider to optimize the settings we have by default- for example :

For PublishQueue we can set DaysToKeep to 2 and for EventQueue we could set the IntervalToKeep to 04:00:00, but this will vary as required, this is applicable when we see we have more records present in both the tables and as a result we started seeing timeouts and DTU spiking issues.

We can also execute SQL script which is available on CMS tuning guide to remove the records from both the tables.

This is really important to keep an eye on the Publish and EventQueue table so that we can avoid any possible downtime issues and make the system stable as much as possible, obviously- there could be other reasons also which leads to bad performance and memory issues but this is always worth to check.

Thought of sharing it here- hope it would be useful for someone who experience the similar issue.

Happy learning 🙂

References:

1. https://briancaos.wordpress.com/2016/08/12/sitecore-event-queue-how-to-clean-it-and-why/
2. https://sitecore.stackexchange.com/questions/10176/effective-way-to-resolve-the-eventqueue-cpu-and-memory-load-related-issue