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