User profiles, database sizes and DaysWorthOfActivityFeedsToKeep

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
Comments (4)

  1. ACH1LLES says:

    Do you mean the Feb CU?

  2. TroyB says:

    Hi Ron,

    I also had a problem with the size of the table InstanceData, which was partially resolved by installing the Feb 2012 , but I needed a way to reduce the database size further, I did this by changing a property on a timer job. I've written a post about it at…/sharepoint-user-profile-sync-database.html

  3. Chris says:

    What is the procedure for shrinking the SyncDB MDF then? run the PS script, then kick off the Activity Feed Cleanup? …or is there more to it than that (this procedure didn't shrink my MDF file).

  4. TroyB says:

    Hi Chris,

    Yes you are correct the script won't shrink your database files. You will need to shrink them via Microsoft SQL Server Management Studio.

    The reason I didn't include that in my script was because I was running it when creating my farm. So the DB files never grew.