Sign in
 
HomeOnline20132010Other VersionsRelated ProductsLibraryForumsGallery
TechCenter >
> 
> 
FIM SyncDB size out of control in Sharepoint 2010

الإجابة FIM SyncDB size out of control in Sharepoint 2010

  • Thursday, May 19, 2011 10:28 PM
     
     
    Sign In to Vote
    0

    We've been piloting MySites in an organisation with about 1500 users. Our SyncDB has been steadily growing and is now over 32GB, by far the largest part of our intranet.

    This thread:

    http://social.technet.microsoft.com/Forums/en-IE/sharepoint2010setup/thread/70df46de-6dc3-4b2c-8cc6-70b3ff3eb3f4

    decribes the issue, but the resolution offered is weak, and doesn't address the essential issue, which seems to be that Sharepoint 2010 installs a cut-down version of FIM that does not include any maintenance tasks. As a result, the SyncDB database continues to grow at a rapid pace.

    Can anyone provide a link to information on what these tasks do, so that we can replicate them?

    We can't take this pilot to production if we have runaway database growth issues.

    Cheers,

    Dylan

     

    Back of the net...
     

All Replies

  • Monday, May 23, 2011 3:56 AM
     
     
    Sign In to Vote
    1

    Hi Dylan,

     

    Firstly, you can use SQL query to determine each table’s size in the Sync database, such as ObjectsInternal and InstanceData.

     

    Secondly, I would like to talk about the FIM Architecture. FIM Synchronization Service as the central component necessary to synchronize data across multiple connected data sources, the synchronization service aggregates information about identities into the metaverse and provides an agentless method for connecting to each data source. The FIM Synchronization Service is the fulfillment mechanism, creating and maintaining identities in other systems whereas the FIMServer service enforces policy on those identities.

     

    The FIM Synchronization Service uses the SQL Server 2008 database engine to store data from connected data sources in a local copy called connector spaces. Information is brought into the connector spaces so that the synchronization service can compare the current state against previously known states. The metaverse, in particular, stores the aggregated state of identities across all connected data sources. In this way, the FIM Synchronization Service enforces convergence across multiple connected data sources.

     

    Finally, some has done a test about FIM service database size data point, you can get some hints from the following article:

     

    http://blogs.msdn.com/b/darrylru/archive/2010/02/04/fim-service-database-size-data-point.aspx

     

    For more information about determining SQL server table size, please refer to the following articles:

     

    http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx

     

    http://serverfault.com/questions/13694/how-do-i-determine-the-size-of-my-sql-server-database

     

    For more information about How user profile synchronization works in SharePoint 2010, please check the following article:

     

    http://blogs.msdn.com/b/spsocial/archive/2010/05/04/conceptual-view-of-how-user-profile-synchronization-works-in-sharepoint-2010.aspx

     

    Thanks,

    Rock Wang

    Regards, Rock Wang Microsoft Online Community Support
     
  • Monday, May 23, 2011 10:21 PM
     
     
    Sign In to Vote
    0

    Thanks for the link to the databse sizing blog post.

    I had a look at it. In the comments Darryl says:

    Darryl Russi

    19 Jan 2011 5:22 PM

    The database used in this reference contained all the requests for the initial population of that database along various additional requests to do things like register all users which we used a test tool to populate automatically.  Typically the completed requests will expire & then be deleted from the database by a SQL Agent job automaticaly over time.

    and someone else mentions a "DeleteExpiredSystemsObjects job"

    but we don't have that job on our server.

    How do we go about deleting the expired system objects? (Or reinstating that job?)

    The largest tables in the database are:
    mms_step_object_details   (82% of data size)
    InstanceData (16% of data size)

    The rest of the database only occupies <2% of the allocated data size.

     

    Back of the net...
     
  • Friday, May 27, 2011 4:53 AM
     
     
    Sign In to Vote
    0

    Hi Dylan,

     

    Where did you find the DeleteExpiredSystemsObjects job? In the job definition?

     

    You can use the following steps to find this job:

     

    1.Open SQL Server Management Studio, and then connect to your database.

     

    2.Click SQL Server Agent, and then click Jobs.

     

    http://technet.microsoft.com/en-us/library/ff830030(WS.10).aspx

     

    Thanks,

    Rock Wang

    Regards, Rock Wang Microsoft Online Community Support
     
  • Monday, May 30, 2011 10:45 PM
     
     Answered
    Sign In to Vote
    0

    I did actually locate the job, so that was probably my fault. However it didn't help at all.

    Instead we ended up running a script that truncates some of the tables, including mms_step_object_details.

    This has resolved our space issue, though the table had grown by another 1.2 million rows (about half a gigabyte of reserved space) since the end of last week.

    This is an unsupported approach to the issue, as far as I am aware, but didn't have any ill effects that we could discern.

     

    Back of the net...
     
  • Thursday, September 22, 2011 10:24 PM
     
     Answered
    Sign In to Vote
    1

    I worked with Microsoft on this issue and they gave me a supported workaround which I blogged about here:

    http://paulliebrand.com/2011/05/26/user-profile-synchronization-database-growing-out-of-control/

    Hopefully they put a proper fix in at some point.

    Paul Liebrand
    @PaulLiebrand
    http://www.paulliebrand.com 

    • Marked As Answer by Dylan Tusler Thursday, September 22, 2011 11:03 PM
    •  
     
  • Thursday, September 22, 2011 11:03 PM
     
     
    Sign In to Vote
    0

    Paul, you are a legend.

    Thanks for persevering with MS about this issue.

    I had long suspected that this might be the kind of "resolution" that we would see.

    Any hints on setting up a new schema? Is that something that can be scripted easily? (I ask without even checking myself, sorry.)

    Dylan.

     

    cras amet qui numquam amavit quique amavit cras amet.
     
  • Sunday, April 15, 2012 6:34 PM
     
     Answered
    Sign In to Vote
    0

    the TSQL provided here is NOT supported.

    Please see the following post for the solution to this issue:http://harbar.net/archive/2012/04/15/345.aspx

    Cheers
    Spence
    www.harbar.net
    Microsoft Certified Master | SharePoint 2010
    Microsoft Certified Master | SharePoint 2007

    • Marked As Answer by Dylan Tusler Sunday, April 15, 2012 10:47 PM
    •  
     
  • Sunday, April 15, 2012 10:47 PM
     
     
    Sign In to Vote
    0

    Thanks for the update, Spencer.

    The fix you refer to arrived with the Feb 2012 CU, which wasn't around when this post was originally made in May 2011.

    We haven't tried it yet, but I expect anyone calling Microsoft on this issue at present will be asked to install that CU rather than run any T-SQL.

    Dylan.

    cras amet qui numquam amavit quique amavit cras amet.

     
  • Thursday, July 19, 2012 8:00 PM
     
     
    Sign In to Vote
    0

    I have installed the February 2012 CU but no SQL Agent jobs have been created.

    Any help on this issue would be much appreciated. I am considering giving a try to the previously mentioned TSQL script to wipe the DB because the disk is getting full.