You may have noticed a large spike in your User Profile Service Database size, or just wondered why it’s so large. After doing some investigation, you realize that the ActivityEventsConsolidated and ActivityEventsPublished table are fairly large and consuming the most space. You’d like to know how to shrink these. Fortunately, there’s now a way.
Let’s start with an explanation of what’s happening. You’re populating user profiles with attributes from AD or some other user information source. This populates the newsfeeds with memberships, which is stored in these tables. This process fills up the profile DB and delays your profile population process. The Activity Feed Cleanup job removes data from these tables based on a expiration value. Out of the box, we set this value to 14 days, with no way to change it. In the August 2011 CU for SP2010, we expose a way to change that value. There’s a new property called DaysWorthOfActivityFeedsToKeep for the UPSA object. Below is a sample PS script that shows how to change that value.
$ups = Get-SPServiceApplication <GUID of UPSA>
$ups.DaysWorthOfActivityFeedsToKeep = 4