All good things come to an end…

I am pleased to announce a life long ambition of mine has come true and I’m shortly due to join Microsoft, UK.

After my second spell working at Trinity Expert Systems I leave with great memories working with some fantastic people and a company with no doubt a great future ahead. Not only have I worked with some great people within Trinity I have met some great clients who have made the last 4 years fly. If my time at Microsoft is as good as it has been at Trinity I am sure to enjoy the next few years ahead.

I’m looking forward to the challenge ahead and thank all those who I have worked with who have helped me get to where I am today.

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.