My SharePoint World! - Nishant Shah

How-Tos, Thoughts and Experiences with Microsoft SharePoint !

Troubleshooting User Profile Sync Issues in Office SharePoint Server 2007

Troubleshooting User Profile Sync Issues in Office SharePoint Server 2007

  • Comments 58
  • Likes

 

In SharePoint Server 2007, we often run into situations where some attributes of a user is updated in Active Directory however the changes don’t reflect in SharePoint Sites even after doing Profile Import.

Lets take an example of a user "Susan Burk" who gets married and her last name changes to Johnson so her name becomes "Susan Johnson".

Active Directory (AD) folks changes the Last Name and Display Name of Susan

SharePoint Admin does a full / incremental profile import and we see that the changes have reflected in her User Profile as well.

 

 However, a week after Susan loges a call with IT Helpdesk stating on the company's Intranet portal, she is still listed as "Susan Burk" and it has not changed to "Susan Johnson".

 

 

SharePoint by default runs "Profile Synchronization" (PROFSYNCH) job every 1 hour and "Quick Profile Synchronization" (SWEEPSYNCH) job every 1 minute which are responsible to sync'ing any changes from Profile Store to SharePoint Sites. So, SharePoint Admin verifies these jobs are in-tact and are running on schedule for the affected web application by going to Central Admin > Timer Job Status and confirms both Profile Sync and Quick Profile Sync jobs have ran within the last hour.

 

So, there are quite a few things which can cause such a scenario.

  • Database is marked as read-only in SQL Server
  • Database status set to "Offline" at Central Administration > Application Management > Content Databases > Manage Content Database Settings
  • Site Collection is marked as read-only in Central Administration > Application Management > Site Collection Quotas and Locks
  • Profile Import is running from a long time
  • Configuration Cache is stale
    • 'cache.ini' file not updating at

Windows Server 2003 - C:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config\<GUID>\cache.ini

Windows Server 2008 & 2008 R2 - C:\ProgramData\Microsoft\SharePoint\Config\<GUID>\cache.ini

  • User is not active in SharePoint
    • "tp_IsActive" attribute is 'False' in Content Database's "dbo.UserInfo" table
  • Web Application is excluded from sync

 

We know its NOT SUPPORTED to make any direct changes / modifications to SharePoint databases (http://support.microsoft.com/kb/841057)

 

Here are the standard troubleshooting steps for the scenarios mentioned above, running all the below steps may / may not be required in every case, so you should identify the problem and apply corrective steps accordingly -  

  • Ensure the Content Database is not marked as read-only in SQL Server
  • Ensure Site Collection is not marked as read-only at Central Administration > Application Management > Site Collection Quotas and Locks
  • Profile Sync wont happen it the Profile Import is running, so ensure Profile Import job has successfully completed and you have a import schedule which is not overlapping (and hence profile import is not running all the time)
  • Run the following SQL read-only query against the SSP Database to identify the Sites which have "Moving" attribute set to 'True
    • SELECT* FROM [SharedServices1_DB].[dbo].[SiteSynch] Where Moving ='1'
  • Run the following command against each of the Site Collections you find which have "Moving" attribute set to 'True' or '1'
  • Run the below commands on a WFE which hosts Central Admin in the following order
    • stsadm -o sync -synctiming m:10 (If you have large number of users in a Site Collection where you're experiencing the issue, increase the number to h:2 {2 hours} or so such that the sync job doesn’t overlap and sync job gets sufficient time to execute)
    • stsadm -o sync -ignoreisactive 1 (switch onlyavailable in builds 12.0.6314.5000 and above)

Note: Setting ignoreisactive to '1' or 'True' will trigger all the Inactive users in the Content DB to get sync'd, which may be CPU intensive and cause a spike / transaction log growth on DB server, its important to revert it back once we are done

The actual performance impact caused by 'ignoreisactive' is determined by

  • Total number of user entries in userinfo tables of each content database.
  • The ratio of inactive/active users in userinfo table
  • Current topology architecture and hardware configuration, e.g. it will ease the perf hit if customers split the content DBs and SSP DB to separate physical hard drives, and/or they separate the SSP DB from content DBs to different physical SQL server etc.
  • How frequent they schedule the User Sync job. It's highly recommanded to set the sync job to run once everyday at non-working hour or in a test environment to evaluate the performance impact first. After understanding the SQL Disk I/O, CPU usage etc., then make the decision on how frequent and when to run the job based on business needs, and moving on to production system.
  • stsadm -o sync -deleteolddatabases 0
  • stsadm -o sync
  • If you notice none of the site collection in the entire web application are sync'ing then you may have excluded the web app. You can run the below command to correct that and get the site collection in web app to sync again.
    • Stsadm -o sync -excludewebapps ""

Note: We are passing a NULL list (observe there is no value between quotes) as there is no way to remove a specific web application from this list. So, lets say you have added 2 web applications to exclude and just want 1 to be included back then you have to remove both and then add 1 again which you don’t wish to sync)

 

Now wait for ~30 to 60 mins and verify if profile sync is working (you may need to wait longer if there are lot of users in the Site Collection so that all entries are updated)

  • Once confirmed its working fine; it is very important to revert back the changes to defaults
    • Set ignoreisactive back to 0 by running
      • stsadm -o sync -ignoreisactive 0
    • Set the Profile Sync job back to default time of 1 hour by running
      • stsadm -o sync -synctiming h:1

 

Note:  If you observe that nothing of the above has worked, try the below 2 steps

  • Detach and attach the Content DB (perform this step in a TEST ENVIRONMENT first to understand any impact prior doing on Production)
  • Try adding the user directly (not thru a Active Directory / SharePoint Group) and check

 

 

 

  • Neatly structured .. really helpful for troubleshooting !! \m/ awesome :)

  • Perfect, thanks for the guide - helped me alot!

    Just one note/correction - you wrote:

    "stsadm -o deleteolddatabases 0"

    you forgot the operation the correct stsadm line is:

    "stsadm -o sync -deleteolddatabases 0"

    Cheers,

    Gernot

  • Thank you Gernot. Corrected :)

  • cool blog..very helpfull

  • THANKS! this resolved the issue for us. Nice to see a start-to-finish documentation of a known issue for once.

  • Thanks a lot ! Profiel Sync Issue is resolved

  • Thanks a lot ! Profiel Sync Issue is resolved

  • <updated the blog for better clarity and include 1 more scenario>

  • I recently had a similar, but different, issue with profile synchronizations. I was unable to find anything online that helped me out with the solution, so I contacted Microsoft for help. For lack of a better place to post this information, I'll post here with the hope it will potentially help others.

    Problem #1: Scheduled profile import jobs stopped running. I could manually run the import jobs, but they would not run as scheduled.

    Solution #1: First a little background. I am running a multi-server farm where I have a dedicated search/central admin server, two web front ends, a SQL Reporting Services server, and a Project Server. As I was digging around in the content database for the web application that my import jobs were not working for I discovered in the dbo.TimerLock table that my project server held the timer lock on my content database. I thought that was odd...that a query server should be holding the lock, not the project server. I verified this was correct with the MS support tech. To resolve this I disabled the timer job service on the project server for about 15 minutes. Eventually the timer lock transferred to a query server and the profile imports started running as scheduled again.

    Problem #2: Profile synchronization was not running between MOSS and WSS for a specific web application.

    Solution #2: In trying to troubleshoot Problem #1 above, I had run the stsadm -o sync -excludewebapps command on my extranet web app. I was noticing that, even though the profile imports were running, the SharePoint profiles had a person's LDAP user name instead of their display name. I was unable to find anything online as to how to counter the excludewebapps command. I finally got the following from MS support:

    1) Stsadm –o sync –listolddatabases 0

    2) Stsadm –o sync –deleteolddatabases 0

    3) Stsadm –o sync –synctiming m:1

    4) Stsadm –o sync –sweeptiming m:1

    5) Stsadm –o sync –excludewebapps “”

    The last command was the one that reset the profile synchronization so all web apps were again included.

    Hopefully other folks don't run into the same issues I did, but if so, hopefully this helps!

  • Hello norski0,

    Thanks for posting the info, it may certainly be useful to some visitors!

    Scenario 1 was not included as its about profile import which is different than profile sync and hence unrelated here. scenario 2 is already included in the above troubleshooting steps

  • Excellent blog Nishant.  Thanks a lot :).

  • I have issues in User Profile in MOSS 2007,

    Background: User Profile Import Schedule was not running as per configured Schedules (this still exists).

    I manually ran the Incremental crawl which pulled the users from different ADs.

    Profile Synchronization, Quick Profile Synchronization  Jobs are running successfully.

    Problem:

    Imported User profile details (changes) are not getting published to the web applications.

    I am able to see the changes in SSP database in “UserProfileValue“ table.

    On the other hand when I removed the user from site and added him back the changes are reflected in the web application.

    Any help would highly appreciated.

    Please reply for any clarifications.

    Thanks in advance.

  • @Muralidharan: Same scenario is described in above blog. Please follow the above blog and it should help to resolve the profile sync issue.

  • This is incredible.. Thanks a ton !!!

    Keep posting such blogs..

  • Nishant, this is great!

    However, could you please let me know the query that you published before to find the names of the databases that has the Moving Status set as True?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment