Recently I noticed on a situation where the sync jobs were failing on a MOSS implementation. Everything else was working great except for the features that surface the "Sites that you are a member of" and "My SharePoint Sites" in office. Looking into the ULS logs I found:

OWSTIMER.EXE (0x07E8)                       0x040C    SharePoint Portal Server          User Profiles                     8zzy    Medium      Aborting sweepsynch for guid instance due to null or non-online content database    

Turns out that the content database had been set to "Offline" to prevent the creation of new sites. Changing the content database to "Ready" status and setting the site limit to the current number of sites solved the problem and now new site memberships are working correctly.

FYI, You may also need to manually delete the old sync databases with "stsadm -o sync -deleteolddatabases 0" but be sure to read these two excellent posts first.

Andrew Woodward: http://www.21apps.com/2008/02/user-profiles-why-do-my-changes-not.html

Paul Liebrand: http://liebrand.wordpress.com/2007/11/29/office-2007-and-my-sharepoint-sites/