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.
Hey Joanne-
I just wanted you to know that this post really really helped our team. We had to do a full sharepoint restore to recover missing profiles. Thanks to your post….we knew how to provent them from being deleted again. Very much appreciated!
Hey Joanne
we faced same situation as you are facing in above article, we disabled the My Site Cleanup Job and rerun incremetntal profile synch job multiple times but still userprofile table shows 16000 entries as deleted using above query
in new connection it imported 16500 user but still it is not reflecting in user profile it is showing old enries. any idea.
Absolute lifesaver, I have been struggling with this for 3 days and thought I was going insane. Was just about to raise a call with Microsoft until I read this
And by the way, I agree this is a DANGEROUS BUG! I have spent, sorry wasted hours restoring VM’s from Snapshots, re-running imports, etc. I eventually had to import all the profiles and then disable all UserProfile Services (Including The Mysite timer job). Next week I will attempt your solution and update these comments
Thanks Joanne, this helped us out hugely.
After reading your post and then also the Technet article: http://technet.microsoft.com/en-us/library/ff681014.aspx#resetSync, we realised we had to disable the “My Site Cleanup Job”… this however was after we’d realised our profile info had suddenly disappeared. Luckily we did have everything backed up prior to this so we tried it all again with the job disabled and it worked.
Cheers
Pete
I do have similar issue, Not sure how the UPS DB restoration will work. I checked one of an MSDN article to restore the profile, social and sync DBs
We did not restore the sync DB, we created the new UPS reusing the existing social and profile DB and then chose to just run the profile synchronization to populate the sync DB . I’m not sure what your asking.
A huge Thank You for taking the time to post this. We were dealing with the exact same scenario and behavior and were at wits end spending countless hours trying to figure out what was happening before we stumbled across this post. I can’t believe bugs like this still exist in SharePoint 2010.
Hey Rob … glad it helped. Months after we discovered this bug, Microsoft did put out a TechNet article with the advice to disable the MySite Cleanup job. Link – http://technet.microsoft.com/en-us/library/ff681014.aspx#resetSync
Everything is very open with a precise description of the issues.
It was really informative. Your website is extremely helpful.
Thanks for sharing!
This still happens in Service Pack 2. I just recreated my UPS and run into the same issue, good thing i had a backup.
Joannecklein
I blundered over this and did exactly what you did.
Now I have recreated the UPS, attached to existing DBs and all the user profiles have been reimported. Everything appears okay.
But what happened to the user mysites after 14 days? Where they deleted or did they remain in place?
Thanks
Ross
Hi Ross,
If you’ve disabled the MySite cleanup job and have ensured that all of the user profiles incorrectly marked for deletion have been ‘unmarked’, then you are safe to enable the MySite cleanup job. There will be no unintentional user mysites deleted after 14 days.
Hope this helps,
Joanne
Hi Joanne
Thanks for your reply.
I have disabled the MySite cleanup job, this was done fairly quickly after the initial mistake. How do I unmark a user profile / mysite for deletion?
The only process I have found appears to involve directly editing the database tables which is unsupported.
Regards
Ross