I have a MOSS publishing site. Content is quite small. I noticed that content database size is now 5GB without any significant content changes made. Using a script to show the tables' sizes, I noticed that the EventCache table size alone is over 3.5 GB. Why is this? what is the reason for this? What to do about it to reduce the size?
did you checked the following things: 1. Did you migrated your content DB from a previous version (SPS 2003)? - In this case, it might be sometimes the timerjobs to clear the eventcache table on its expiries date does not perform well and rows older than the defined number of days still remaining in the table.
In MOSS Sp2 we fixed some parts which also causes an unexpected growth of databases but not sure if this applies also to the eventcache table in certain.
In one case I had, the free diskspace on SQL Server did not have enough space to perform the creation of the temp DB which is built during the proceeding of eventcache ta ble to delete expired rows. We could figure out in that case with an 75GB DB where eventcache allone tooks 8GB that we had free up space of about 260GB to clear the table.
Another hint may find here as well: KB Article: 957691 - Build 12.0000.6331.5000) "Supported method to clear the EventCache table"
Hope this may help you,
greets, Steve Chen, Support Engineer SharePoint EMEA GTSC
I recently had the same problem due to database corruption. According to this site, there is a timer job that should be deleting all old events, and you should make sure this "Immediate Alert" timer job is running.
You can check to see if old events are actually being deleted by running this SQL against your database:
select top 1000 from [dbo].EventCache order by id desc
Then, check to make sure there are no events with dates over a week or two.
If you do have such events, pick the largest ID from the query above, and then run (let's assume the ID was 8500):
delete from [dbo.EventCache] where id < 8500
In my case, I received an error indicating that the database was corrupt (and this was the reason I had so many rows in my database).
To fix the SharePoint database corruption, you can run the following (Important: this may cause some data loss):
ALTER DATABASE WSS_CONTENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DBCC CHECKDB ('WSS_CONTENT', REPAIR_ALLOW_DATA_LOSS); ALTER DATABASE WSS_CONTENT SET MULTI_USER
(My Content database was called "WS_CONTENT", yours may be different).
Then, run the "Select" statement above to make sure there are not too many rows, and if so, delete them.