We discovered a bug while deleting/recreating our existing User Profile Service Application in SharePoint 2010. I am writing this post to share my experience with hopes that it may help you if you have encountered the same issue.
We implemented our SharePoint 2010 environment about a year ago and had discovered the User Profile Service Application wasn’t as stable as we had hoped: The synchronization database was growing out-of-control (basically doubling every 6 months), the User Profile Synchronization service periodically stops, and it seems generally ‘finnicky’. (How’s that for a technical term?) We synchronize our profile data from Active Directory and have roughly 1200 User Profiles.
Note: this post is NOT about how to configure the User Profile Service Application. For that, there are many excellent posts out there. (Refer to this article by Spencer Harbar)
To recreate the User Profile Service application, we deleted it, but did NOT select the checkbox beside ‘Delete data associated with this service application.’
Once this was done, we recreated the User Profile Service Application reusing the same Social and Profile databases as before, but specifying a *NEW* database name for the synchronization database. Once all the configuration was complete (refer to blog post above), we ran the first user profile full synchronization.
Lo and behold …1200 User Profiles were imported. Everything was looking great. We then scheduled the recurring Incremental sync to occur every day at 1 AM from that point on and left for the day. When we came in the next day, almost half of the User Profiles had been deleted!!!! How could this happen?
What we didn’t realize is the incremental sync that occurred at 1 AM had marked almost half of the User Profiles as ‘Deleted’, even though they weren’t actually deleted in Active Directory. Yikes! Along comes the hourly (by default) My Site Cleanup Job whose function is to purge User Profiles marked for deletion. This job did exactly what it was supposed to do and deleted almost half of the User Profiles (since they were marked for deletion after the synchronization)
We couldn’t believe our eyes. We initially thought that the User Accounts had perhaps been deleted in Active Directory, but discovered that this was not the case. We tried this scenario over and over again in our test environment restoring from recent backups of the profile and social databases and using a new sync database. We also tried many combinations of full and incremental syncs, but there didn’t appear to be a pattern to prove which sync was causing the user profiles to get incorrectly marked for deleteion. Each and every time, a seemingly random large number of the User Profiles would be marked for deletion. I don’t know the reason for why they were marked for deletion (that is for someone who understands the inner workings of Forefront Identity Manager to explain), but I do now know how to get around this dangerous bug.
What was discovered is if you temporarily DISABLE the My Site Cleanup Job timer job prior to running the first full synchronization and then run several incremental synchronizations immediately after, the User Profiles incorrectly marked for deletion will eventually in fact be ‘unmarked’. To confirm, this you can run this sql query against the profile database to return a list of all User Profiles marked for deletion after running each synchronization:
Select * from userprofile_full (nolock) where bDeleted = 1
IMPORTANT: Please make sure you ENABLE the My Site Cleanup Job once you have determined that the incremental syncs are correctly updating the User Profiles that should be deleted as this job is integral to removing the User Profiles once users are legitimately deleted from the directory store. Note: in most of our tests, at least three synchronizations would have to run before the user profiles were no longer getting incorrectly marked for deletion.
Here is our environment in case this issue is environment-specific:
Microsoft SharePoint Server 2010, SP1, August CU, Active Directory is the Sync Connection, SQL Server 2008 R2
Hope this post helps you.