The following tables can contain a lot of data, especially if the website creates and or edits a lot of items, for the first 2 the tables it is very simple to define how many days to keep the data.
- Publish Queue
- Event Table
- History Table
If there is to much data in any of the table it can causes the performance issues for Sitecore. By default Sitecore stores data for 30 days, which is too long.
Sitecore uses the following scheduled task to clean the database tables.
- Sitecore.Tasks.CleanupPublishQueue
- Sitecore.Tasks.CleanupEventQueue
- Sitecore.Tasks.CleanupHistory
Both CleanupPublishQueue & CleanupEventQueue tasks provide the ability to specify the number of days to keep the entries (see below). Unfortunately in some cases 1 day is too long, but I will get back to that in another post.
So why can’t you specify the number of days for the Sitecore.Tasks.CleanupHistory?
Just in case it was another undocumented feature, I opened up reflector and took a look at the Sitecore.Tasks.CleanupHistory implementation to see if it was possible or not to specify the period of time to keep entries in the history table – It wasn’t!
I continued my search and found that the SqlServerHistoryStorage.Cleanup() function (see image below) is responsible for cleaning the history table. The SQL statement uses the EntryLifeTime property to define the threshold (time-span) to keep the entries.
Solution
With a bit more investigation I found that it was possible to define the EntryLifeTime value for each database in the web.config. It is possible to define the time-span using days, house, minutes s and even seconds 🙂
Hope this helps, Alan
Hi Alan. I’d like to ask you about the possibility how to decrease the time amount to less than 1 day in case of PublishQueue (or EventQueue). You’ve mentioned that it will be a subject of some next post :). Do you have any idea? As far as I saw the CleanupPublishQueue code, I would say “no way” except of the custom cleanup agent where a value of 0 is allowed as well…
Thanks in advance, rene.
We used a scheduled SQL script to clean up every 3 hours, not nice but it works
Thank you for the reply, Alan. I understand… 🙂
Thanks. This helped today.
Pingback: Sitecore Audit Trail – the easy way | Sitecore notes - Stelio Di Bello
History table is supposed to keep the content management and content delivery instances in sync not sure it’s I try to be used to track item content changes
Pingback: Sitecore client and logon is very slow (properties table AGAIN) | Alan Coates – Sitecore/.NET blog