Troubleshooting User Profile Sync Issues in Office SharePoint Server 2007

 

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 (https://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