Sitecore Event Queue – How to clean it

STEP 1: CLEAN OUT EVENT QUEUE, HISTORY TABLE, PUBLISH QUEUE

The following SQL statement will clean out the history table, publish queue and event queue, leaving only 12 hours of history and publish data and 4 hours of events. Replace YOURDATABASE with the name of your database:

/****** History ******/

delete FROM [YOURDATABASE_Core].[dbo].[History] where Created < DATEADD(HOUR, -12, GETDATE())

delete FROM [YOURDATABASE_Master].[dbo].[History] where Created < DATEADD(HOUR, -12, GETDATE())

delete FROM [YOURDATABASE_Web].[dbo].[History] where Created < DATEADD(HOUR, -12, GETDATE())

  

/****** Publishqueue ******/

delete FROM [YOURDATABASE_Core].[dbo].[PublishQueue] where Date < DATEADD(HOUR, -12, GETDATE());    

delete FROM [YOURDATABASE_Master].[dbo].[PublishQueue] where Date < DATEADD(HOUR, -12, GETDATE());

delete FROM [YOURDATABASE_Web].[dbo].[PublishQueue] where Date < DATEADD(HOUR, -12, GETDATE());

     

/****** EventQueue ******/

delete FROM [YOURDATABASE_Master].[dbo].[EventQueue] where [Created] < DATEADD(HOUR, -4, GETDATE())

delete FROM [YOURDATABASE_Core].[dbo].[EventQueue] where [Created] < DATEADD(HOUR, -4, GETDATE())

delete FROM [YOURDATABASE_Web].[dbo].[EventQueue] where [Created] < DATEADD(HOUR, -4, GETDATE())

STEP 2: CONFIGURE THE SYSTEM TO CLEAN THE TABLES MORE OFTEN

With the system stabilized, we need to take more care of the table sizes.

HISTORY TABLE:

Sitecore is already configured to clean the tables so they only contain 12 hours of data. 

12 hours of data is usually what any SQL server will handle, and you will have up to 10.000 rows in the table.

<Engines.HistoryEngine.Storage>

  <obj type="Sitecore.Data.$(database).$(database)HistoryStorage, Sitecore.Kernel">

    <param connectionStringName="$(id)" />

    <EntryLifeTime>00.12:00:00</EntryLifeTime>

  </obj>

</Engines.HistoryEngine.Storage>

PUBLISH QUEUE:

Sitecore keeps 30 days of publishing queue. If you insert and update items often, you should lower this number. For each item change (including any change that the system does) is stored here.

<agent type="Sitecore.Tasks.CleanupPublishQueue, Sitecore.Kernel" method="Run" interval="04:00:00">

  <DaysToKeep>2</DaysToKeep>

</agent>

EVENT QUEUE:

The event queue is the most important table to keep small. In a distributed environment, each server will read the contents of the table every 5 seconds, using a time stamp stored in the If you can keep the number of rows below 7.000, most SQL server should be able to handle that amount of data. Even smaller numbers are preferred as well. Properties table as key. Any row before the timestamp will not be read.

You, therefore, need enough history to cater that a server will be offline for a while, but at the same time so little contents that any read and write will be amazingly fast.

Before Sitecore 8.1, Sitecore would only allow you to clean events older that 1 day. This is way too much, especially if you publish often. The new IntervalToKeep will allow you to determine the hours to keep as well:

<agent type="Sitecore.Tasks.CleanupEventQueue, Sitecore.Kernel" method="Run" interval="04:00:00">

  <IntervalToKeep>04:00:00</IntervalToKeep>

  <DaysToKeep>1</DaysToKeep>

</agent>