*** 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.
