Every SharePoint content database has an EventCache Table. Changes to the SP objects contained in the content database are logged to this EventCache Table. Every change is added as an entry/row to the table. EventCache table is the change log for objects in database.
Columns in EventCache Table:
|Column name||Data Type||Description|
|EventTime||datetime||Time when the event record was inserted into the database.|
|SiteId||uniqueidentifier||ID of the site, available from the Sites table.|
|ListId||uniqueidentifier||ID of the list in which the monitored item appears.|
|ItemId||int||ID of the item that raised the event.|
|ItemName||nvarchar||Full name of the item.|
|ItemFullUrl||nvarchar||Full path to the item|
|EventType||int||ItemAdded (1), Item Modified (2), Item Deleted (4), DiscussionAdded (16), Discussion Modified (32), Discussion Deleted (64), Discussion Closed (128), Discussion Activated (256).|
|ModifiedBy||nvarchar||User name of the person whose modification raised the event.|
|TimeLastModified||datetime||Time when the event occurred.|
Reference to Columns in EventCache Table: https://msdn.microsoft.com/en-us/library/dd585124%28v=office.11%29.aspx?f=255&MSPPError=-2147217396
This table can be queried directly to find out changes to any particular SP object like a list/library by knowing its GUID.
SELECT * FROM EventCache (NoLock) WHERE ListId = ‘GUID’
Here is a post which lists all the EventType codes and their descriptions.
Result of the above query will return when the items in that particular list were changed.
EventCache table can grow huge and occupy a lot of database storage. Like any other log, this is also controlled by a pair timer jobs.
Change Log: The Change Log records many different types of changes made to SharePoint sites. This timer job is used to periodically delete old entries from the log. This job is scheduled to run weekly by default.Instance of this job runs for each web application. Expiration of change logs is dependent on ‘ChangeLogExpirationEnabled‘ and ‘ChangeLogRetentionPeriod‘ properties of Web application.
Immediate alerts: Sends out immediate and scheduled alerts. This job process all the events in change log and send out alerts based on user subscriptions. After sending the alert, it marks the event entry as processed.
Note: Unless the event entry is marked as processed by Immediate alerts timer job, Change Log timer job will not delete the entry. It is very important to understand that, even though Change Log timer job is running, EventCache table may not be cleaned up and can grow huge unless the Immediate alerts job is running.
If your EventCache Table is huge, make sure that Immediate alerts timer job is scheduled to run along with Change Log job.