In SharePoint every content database contains an EventCache table that is the “change log” for objects contained in the database. Each row in the table is depicting a change in an object. Columns in the table contain information such as the date and time of a change, the type of object that was changed, the nature of the change, and a unique identifier for the object.

Sharepoint has a “Change Log” Timer job for each web-application which is scheduled to run on a Weekly basis. This job removes expire entries from the Change log of the respective web-application.

Expiration of the change logs will happen based on the ChangeLogExpirationEnabled and ChangeLogRetentionPeriod properties of the web application. Timer job called “Immediate alerts” which then processes all the events in the change log and & send out alert to users who have subscribed alerts for. This timer job then marks the EventCache entry as processed and updates the last processed event details in EventBatches table

There are situations I have seen where the Event cache table is huge (millions of rows) & they are not being cleaned up as expected. Even when you detach the database from SharePoint & attach it back with –clearchangelog switch the EventCache table is not purged.

One of the reasons this happens is because the “Immediate Alert” timer job is disabled. This would lead to alerts being unprocessed & the Change Log Cleanup job will ignore the unprocessed entries.

One needs to Simply Enable Immediate alerts timer job web application by going to Central admin, Monitoring and Review job definitions page.

Note: Immediate alert job will process couple of 1000s of record at each run. If there are millions of records to be processed and cleaned up, then you may have to schedule the immediate alert and Change log timer jobs to run more frequently than the default schedules.