Having loads of records in the Event Queue table is normal if you have lots of Sitecore items. However, in order to gain into performance, you can cleanup your Event Queue. In this post, I will describe to you the 2 methods that can be used to cleanup the Event Queue table.
The first method is to used the in built sitecore agent, that is, the CleanupEventQueue. By default, it will cleanup the Event Queue table every 4 hours. You can specify the number of days that you want to keep records.
For example, if you set the number of days to 1, the agent will remove all the records except the last day records. The main reason why the Event Queue should be cleanup is because it gets populated more often, hence increasing the size of the database. Also, once an event has completed, it does not get deleted.
When using the CleanupEventQueue agent, the main drawback is that it will use the resource of the instance. Hence, decreasing the performance of your Sitecore Instance. It may be a small decrease in performance but it there are large number of records to be removed from the Event Queue table, the agent will take time to cleanup the table.
The second method is to cleanup the Event Queue table from SQL directly. Below is the SQL statement to cleanup the Event Queue:
WHILE 1=1 BEGIN SET ROWCOUNT 10000 DELETE dbo.EventQueue WHERE [Created] < DATEADD(HOUR, -5, GETDATE()) OPTION(MAXDOP 1) IF @@ROWCOUNT = 0 BREAK END
You can use the above query as a Stored Procedure and set an interval so that it gets run periodically. In the above SQL statement, all records will be removed except for the last 5 hours.
By using the SQL statement, it remove the dependency of the Sitecore application, hence resulting in a more efficient way to cleanup the event queue table.