*** 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.
6 comments:
Thanks Paul, I am sure this tip will come in very handy!
James Brennan
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
@TroyB thanks for posting and sharing. Shame you didn't reference my article in your follow up blog as you found it useful.
Paul
Sorry about that Paul, i've now updated my blog post.
Hi Paul, I've got a client SharePoint 2010 environment that is patched beyond SP2. Logically this means we should be patched against the User_Sync db growing out of control problem but last month (February 2021) the InstanceData table suddenly started growing out of control and the db is now over 15Gb in size. We did have a largish amount of organisational change but we've not noticed this before and cannot afford this to become the new norm (as out physical disk array is at capacity and we cannot afford to buy a new one knowing we're midway through completing a cloud migration).
With SP2010 now almost EoL much of the official documentation has been removed/moved and many links in the posts around this issue no longer exist and as it's a problem I've not experienced I'm relying on the blogsphere and its experts for help.
I've seen some PowerShell (TroyB above) that looks promising but I'd like to get a better handle on how transient the User_Sync db content really is before making changes to how much history is retained. If you can truly safely reset the User Profile Synchronization db then why not set the retention time to 7 days or even 1 day. I note that Troy set his dev instance to 1 day. What is going to break if we set the retention to less that the default 90 days?
Post a Comment