User Profile Sync DB growing out of control

*** UPDATE 20/04/12 ***

February 2012 SharePoint 2010 cumulative update now includes several stored procedures:

“This hotfix package provides store procedures that clean up the Forefront Identity Manager (FIM) history tables during profile synchronization jobs.”

So the only reason you should be carrying out the steps in my post below is if you really need to (and I mean SQL is bursting at the seams) and applying February 2010 CU is some time away as this needs to be tested in non-production environments before releasing.

Information on the February 2012 CU can be found here http://support.microsoft.com/kb/2597150 also make sure you also review and download the SharePoint Foundation February 2012 CU http://support.microsoft.com/kb/2597132.

*** END OF UPDATE ***

Before I dive into this blog a quick question for you SharePoint people – Do you know the current size of all your production databases? and I don’t just mean content databases. Read on…

I recently noticed that just under a year after go-live a production SharePoint 2010 farm user profile sync database had expanded to 12GB. The sync database is for staging imports into the profile database as well as storing configuration information (more information here http://technet.microsoft.com/en-us/library/ee662538.aspx). Therefore its not where the profile information lives long term as its a ‘staging’ database before committing to the profile store.

The impact of this database spiralling out of control was that SQL backups were unnecessarily taking longer as was SharePoint backups thus requiring more space to store and when considering if you retain weeks / months of backups this could have a large impact.

A colleague of mine Paul Hunt pointed me in the direction of this blog post which basically advises that after raising a call with Microsoft this database will continue to grow and there is no timer job (automation) to clear this out. Personally from the blog referred to I’m always nervous of running SQL scripts against databases due to potential issues with Microsoft support.

Reading http://technet.microsoft.com/en-us/library/ff681014.aspx#resetSync this basically states:

you can safely reset a User Profile Synchronization database without losing information in the profile store”

So proceeding with the steps (and testing in Pre Production) to reset the sync database, this did the trick and reduced the database to under 100MB from 12GB which meant happy storage and SQL database teams!

A couple of things to note is that you need to make sure you reset SQL permissions on the Sync DB and also make a note of all the synchronisation connections as the PowerShell script above will remove all connections and subsequent filters which aren't clearly identified from the landing page of the synchronisation configuration.

Finally this isn't a long term fix and as suggested in the blog post earlier a longer term resolution needs to be sought.

As always I welcome your thoughts.

5 comments:

jamesdbrennan said...

Thanks Paul, I am sure this tip will come in very handy!

James Brennan

TroyB said...
This comment has been removed by the author.
TroyB said...

Hi Paul,

I found your article very useful, 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 http://troyvssharepoint.blogspot.co.uk/2012/10/sharepoint-user-profile-sync-database.html

Hopefully this helps someone else

Paul Grimley said...

@TroyB thanks for posting and sharing. Shame you didn't reference my article in your follow up blog as you found it useful.

Paul

TroyB said...

Sorry about that Paul, i've now updated my blog post.

Post a Comment