Paul Liebrand's Weblog

Welcome to my blog mainly about SharePoint

Skip to: Content | Sidebar | Footer

Google Search

User Profile Synchronization Database Growing Out of Control?

26 May, 2011 (14:38) | SharePoint | By: Liebrand

If you have worked with SharePoint 2010 and created a User Profile Service Application (UPA) you may have noticed that your SyncDB is growing larger each and every day. Search around the Internet, you will find posts like this:

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

Other people are definitely having this problem and there are no solutions on how to solve this problem.

I reviewed the Database Types and Descriptions (http://technet.microsoft.com/en-us/library/cc678868.aspx) article on TechNet and it states the following for general size and growth factors:

“Medium to large. Growth factors include the number of users and groups, and the ratio of users to groups.”

Nowhere in this article does it state the database will grow forever with no cleanup process so plan accordingly.

I opened a support ticket with Microsoft on this issue and after a few weeks of troubleshooting I got the following response:

“…The growing of the Sync DB after each sync is expected behavior in SharePoint. My recommendation would be to once a month, delete your UPA (keeping the Social and Profile DB) and recreate the UPA with a new Sync DB thus not allowing the sync DB to grow too large…”

I am sorry – this is not good enough.  I have escalated the issue to the SharePoint product team to get some better clarity or even a possible solution. There appears to be an oversight on the product teams part on this issue.  There has to be a clean up process or something that cleans up the InstanceData table (this is the main culprit).

Have you noticed the growth of this database in your organization?

I’ll report back the results of the escalation once I have something new to report!

</rant>

Update – 6/9/2011

In working Microsoft, they have confirmed that the way the UPA currently works is by design and there is absolutely no process that will clean up the UPA database.  One workaround that was recommended was to simply delete and re-create the UPA services once a month.  Of course, this is asinine – I pushed the issue further to the SharePoint product team and ask them to provide either a hot fix or a supported method to purge these large tables.

Once I hear back from the product team, I’ll post another update.

Update – 7/12/2011

I received word from Microsoft that this issue is now with the escalation team – no more information than that.

One thing I wanted to point out is that in the environments where we saw extreme growth, we deleted the UPA and re-created it, reconfigured it, etc. and the database seems to be growing at a more reasonable rate. These steps were performed in more than one of our environments where we saw huge databases and it settled down. Something you might want to consider trying until Microsoft releases a better solution and/or workaround.

Update – 8/30/2011

I finally have a solution, well – “workaround”, to this problem.  Microsoft provided me with the stored procedure that is missing from SharePoint 2010’s version of Identity Management.

DISCLAIMER: Microsoft has told me that this script cannot be altered in anyway or I run the risk of being unsupported. Please use this at your own discretion.

Before running the script, you need to create a new schema in your SYNC database called FIM (I wonder where this stored procedure came from? haha).

Before running the script, please insure you change the database name to match that of your SYNC DB.

USE [Sync DB]
GO
CREATE SCHEMA FIM
GO
/****** Object:  StoredProcedure [fim].[TruncateInstanceData]    Script Date: 08/10/2011 14:09:11 *****/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [fim].[TruncateInstanceData]
AS
BEGIN

--************************************************************
--*                                                          *                                                         *
--*   Copyright (C) Microsoft. All rights reserved.          *
--*                                                          *
--************************************************************
SET NOCOUNT ON;
DECLARE @truncationTime datetime;
SET @truncationTime = DATEADD(day, -1, GETUTCDATE());
DELETE FROM [dbo].[InstanceData]
WHERE ([created] < @truncationTime)
END

 

After your have created this script, you can create a SQL scheduled job to execute it once a week, once a month, whatever.

Update – 12/29/2011

Depending on how the recovery mode of your database, you might notice that your TEMPDB will increase because of all the transactions that took place during the truncate.  Set your DB to SIMPLE recovery mode prior to running the initial stored procedure, or let your normal maintenance plans run and your databases should return to normal size.

Update – 4/16/2012

This probably has been solved in the February 2012 CU for SharePoint 2010.  The necessary jobs have been added to insure the database is cleaned up. See http://www.paulliebrand.com/2012/04/16/user-profile-database-growth-issue-resolved/


Post to Twitter Post to Delicious Post to Digg Post to Facebook Post to Reddit

  • Gorka

    I find that the growth of the sync DB is on my test farm (over 7GB) while on my production farm, the sync DB is only 300MB.  This is odd since there are thousands more profiles on my production farm.  Thank you for submitting the ticket and following up on this.

  • Microsoft has contacted me with a “possible” workaround that involves a SQL scheduled job.  I’ll be meeting with them soon to figure out what it is and I’ll post back if it is useful.

  • Gorka

    Thanks for keeping up with this Paul.  I look forward to hearing what you find out.

  • Pingback: SharePoint 2010 SyncDB keeps growing out of control « Nils Blomgren's Blog()

  • Theelf55

    I haven’t been able to make this SP work. I’m getting this error “Expecting conversation”. Any ideas?

  • Can you post the exact script and error you are getting?  I have run this stored procedure in multiple environments and it works just fine.  Please make sure you change the Use [Sync DB] line to match the name of your sync database.

  • Pingback: SharePoint Tips - Secrets of SharePoint -()

  • Justin

    Just so I”m clear on what is going on here (not a SQL expert), first we create a schema in the Sync DB called “fim”, then we run the above script, which will create a stored procedure……then when we create a SQL scheduled job, do we have it fire the stored procedure, or the script?  I’m assuming it’s the stored proc, but was a little thrown by your very last statement. 

    BTW, this is VERY helpful!

  • Bingo! It’s a stored procedure, so your SQL scheduled job would just run that stored procedure.

  • Justin

    Just so people have an idea of what to expect, I ran this in my test environment (which isn’t at Prod performance level) and the db was set to SIMPLE recovery…..

    -Original DB size: 4.3 GB
    -Original Log size: 300 MB
    -Processing time: 1.5 hrs
    -New DB size (after shrink): 450 MB
    -New Log size (after shrink): 100 MB

  • Pmaille

    any new on this issue ??? does microsoft plan to release a fix.

  • The latest update on this was posted on 8/30 — it was a workaround provided by Microsoft.  They told me they had plans to fix it but it is WAY low on the priority list.  I personally would not expect to see anything from them anytime soon.

  • Nhusson

    do you know if this can be used on the Search Service Application databases as well? Our Crawlstore keeps growing through the roof.
    Thanks

  • i’m sorry, but how exactly would i go about creating the schema in my SYNC database called FIM? 

  • Ted

    create schema fim

  • Colin Cook

    Our database had reached a whopping 40gb with only 1200 users, i have just run this script which is now 2:30mins in and my database is over 70gb in size, is this to be expected?

    I am hoping that there will be a huge clear out any minute…. now?

  • Colin,

    It can definitely take a long time to run depending on how much you decided to purge. With a database size that large, you might have considered purging your data in batches instead of one huge batch. For example, run the script passing 300 the first time, then run it again passing 250, then again with 200, etc.

    Paul

  • As Ted posted, you simply run:

    “CREATE SCHEMA FIM” in the sync DB and then execute the script outlined above.

  • Colin Cook

    Thanks Paul, 

    This worked better, I ran the script dropping 50 days each pass and it took about an hour each pass, once the logs were cleared our database is now 10gb instead of 40gb and we have set the script to run every night. 

    Our DBA can now sleep happily at night!

  • Marko

    Hi! I’m having same problem like Colin Cook.
    Our sync database was about 3gb and after creating schema FIM and running this script, database increased up to 10GB.

    Marko

  • What recovery mode is this database set too?

  • Vijay

    USE [Sync DB]
    GO

    CREATE SCHEMA FIM

  • Vijay

    After performing all the steps required to bring back Synch DB and Log files sizes in control, i now have tempdb size gone upto 5 GB (this was growing while the above stored procedure was running) which brings the overall disk size back to original state.
    Is this desirable? i.e. tempdb growth due to above operations?
    How to deal with this tempdb size now?

  • What recovery mode is your database set too?

  • Marko

    Database recovery mode is set to simple.
    Maybe it’s a permission issue?

  • Pingback: Honey, who grew my SharePoint 2010 Synchronization Database astronomically « Seth's Blog()

  • Bart Hofstede

    We have about 400 users and a sync db of more than 22 Gb after almost a year. I ran the script witch took about 4 hours. When the script was done i had a Sync db of 65 Gb and a temp db of 26 Gb.

    I restarted the sql 2008R2 server but that does’nt change the tempdb size.
    I shronk the sync db with the command.BEGIN
    DBCC shrinkdatabase ([SyncDB], 2)
    ENDAnd finaly the tempdb with the command.DBCC SHRINKFILE (‘tempdev’, 1024)

  • Bart Hofstede

    We have about 400 users and a sync db of more than 22 Gb after almost a year. I ran the script witch took about 4 hours. When the script was done i had a Sync db of 65 Gb and a temp db of 26 Gb.

    I restarted the sql 2008R2 server but that does’nt change the tempdb size.
    I shronk the sync db with the command.BEGIN
    DBCC shrinkdatabase ([SyncDB], 2)
    ENDAnd finaly the tempdb with the command.DBCC SHRINKFILE (‘tempdev’, 1024)

  • Pingback: User Profile Sync DB Issues | Adventures in SharePoint Development()

  • Aleks

    do NOT use provided script – as Spence Harbar already pointed out, it works anyway with only 2-3 builds of FIM. Another thing – if you run such kind of stuff against SharePoint databases you will bring your farm in unsupported state (unless you are directly said from Microsoft Support to do so). Before Feb 2102 CU, the only supported way was to recreate Sync DB, with Feb2012 CU this problem is solved.
    For more information on this topic, look at Spence’s article:
    http://www.harbar.net/archive/2012/04/15/345.aspx

  • troyb30

    Hi Paul,

    I found your article very useful, but I needed a way to reduce the database size further by change a property on a timer job. I’ve written a post about it at http://troyvssharepoint.blogspot.co.uk/2012/10/sharepoint-user-profile-sync-database.html

  • JeroenS

    Client is running version 14.0.6117.5002 (Feb 2012 CU) and still has this problem (33 GB with 10 users)..

  • Pingback: SharePoint from Scratch » That was nuts!()